Pasting data in next blank cell in range based on criteria in adjacent cell

Question

I am a beginner using VBA.

I want to copy and paste a value to another sheet in the first blank cell in a specific range.

For instance Sheet 1 is a general ledger:

Cell F14 contains text (either Rent, or Cash, or Accounts Receivable etc.)
Cell K14 contains the Debit Amount. Cell l14 contains the Credit amount.

I want to copy and paste the amount in either K14 or K15 to sheet 2 in a specific range using the next available cell based upon the text in F14.

If it "Cash" then the range = sheet2 D1:D10. If Rent then paste to range sheet2 D20:D30 etc.

Any help would be appreciated.

Please use tags more as a direction which technology is used. Feel free to use the formatting options to improve the quality of your question. Also it is advised to show the code that you have written so far, so that people have a starting point to help you. — Kai Mattern

jamesC jamesC · Accepted Answer · 2015-02-09T16:05:42

I think here is some code that gets the next available free cell in a range

Sub capturedata()

Dim sheet1, sheet2 As Worksheet
Dim testValue As String
Dim cashRange, rentRange As Range

Set sheet1 = ActiveWorkbook.Sheets("Sheet1") ' general ledger
Set sheet2 = ActiveWorkbook.Sheets("sheet2")

testValue = sheet1.Range("F14") ' the cell with rent, cash, etc in it

'the ranges
Set cashRange = sheet2.Range("D1:D10")
Set rentRange = sheet2.Range("D20:D30")

Select Case testValue 'based on what is in "f14"
    Case "Rent"
        'paste from k14
        '****I believe the below is the part you're really concerned with***

        For Each cell In cashRange
            If cell.Value = "" Then 'first empty cell
                    cell.Value = sheet1.Range("k14") 'make this more dynamic with a for loop if needed
                Exit For
            End If
        Next cell
    Case "Cash"

        'paste from k15
        For Each cell In rentRange ' make into a function to avoid repeated code
            If cell.Value = "" Then 'first empty cell
                    cell.Value = sheet1.Range("k14")
                Exit For
            End If
        Next cell

    Case "Accounts Receivable"
       'add a for loop here  based on other criteria
    Case Else
    'case not met

End Select


End Sub

Pasting data in next blank cell in range based on criteria in adjacent cell

1 Answers