Using Vlookup to copy and paste data into a separate worksheet using VBA

Question

Alright I'm a beginner with VBA so I need some help. Assuming this is very basic, but here are the steps I am looking at for the code:

-Use Vlookup to find the value "Rec" in column C of Sheet1, and select that row's corresponding value in column D

-Then copy that value from column D in Sheet1 and paste it into the first blank cell in column B of another worksheet titled Sheet2

I've got a basic code that uses Vlookup to find Rec as well as it's corresponding value in column D, then display a msg. The code works fine, and is the following:

Sub BasicFindGSV()
Dim movement_type_code As Variant
Dim total_gsv As Variant
movement_type_code = "Rec"
total_gsv = Application.WorksheetFunction.VLookup(movement_type_code,Sheet1.Range("C2:H25"), 2, False)
MsgBox "GSV is :$" & total_gsv
End Sub

I also have another one that will find the next blank cell in column B Sheet2, it works as well:

Sub SelectFirstBlankCell()
Dim Sheet2 As Worksheet
Set Sheet2 = ActiveSheet
For Each cell In Sheet2.Columns(2).Cells
If IsEmpty(cell) = True Then cell.Select: Exit For
Next cell
End Sub

Not sure how to integrate the two, and I'm not sure how to make the code paste the Vlookup result in Sheet2. Any help would be greatly appreciated, thanks!

Answer 1

So for being a beginner you're off to a good start by designing two separate subroutines that you can confirm work and then integrating. That's the basic approach that will save you headache after headache when things get more complicated. So to answer your direct question on how to integrate the two, I'd recommend doing something like this

Sub BasicFindGSV()
    Dim movement_type_code As Variant
    Dim total_gsv As Variant
    movement_type_code = "Rec"
    total_gsv = Application.WorksheetFunction.VLookup(movement_type_code, Sheet1.Range("C2:H25"), 2, False)
    AssignValueToBlankCell (total_gsv)
End Sub
Sub AssignValueToBlankCell(ByVal v As Variant)
    Dim Sheet2 As Worksheet
    Set Sheet2 = ActiveSheet
    For Each cell In Sheet2.Columns(2).Cells
        If IsEmpty(cell) = True Then cell.Value2 = v
    Next cell
End Sub

That being said, as Macro Man points out, you can knock out the exact same functionality your asking for with a one liner. Keeping the operational steps separate (so actually a two liner now) would look like this.

Sub FindGSV()
    AssignValueToBlankCell WorksheetFunction.VLookup("Rec", Sheet1.Range("C2:H25"), 2, False)
End Sub
Sub AssignValueToBlankCell(ByVal v As Variant)
    Sheet3.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value2 = v
End Sub

Like I said, if you plan to continue development with this, it's usually a good idea to design your code with independent operations the way you already have begun to. You can build off of this by passing worksheets, ranges, columns, or other useful parameters as arguments to a predefined task or subroutine.

Also, notice that I use Value2 instead of Value. I notice you're retrieving a currency value, so there's actually a small difference between the two. Value2 gives you the more accurate number behind a currency formatted value (although probably unnecessary) and is also faster (although probably negligible in this case). Just something to be aware of though.

Also, I noticed your use of worksheet objects kind of strange, so I thought it'd help to mentioned that you can select a worksheet object by it's object name, it's name property (with sheets() or worksheets()), index number (with sheets() or worksheets()), or the "Active" prefix. It's important to note that what you're doing in your one subroutine is reassigning the reference of the Sheet2 object to your active sheet, which means it may end up being any sheet. This demonstrates the issue:

Sub SheetSelectDemo()
    Dim Sheet2 As Worksheet
    Set Sheet2 = Sheets(1)
    MsgBox "The sheet object named Sheet2 has a name property equal to " & Worksheets(Sheet2.Name).Name & " and has an index of " & Worksheets(Sheet2.Index).Index & "."
End Sub

You can view and change the name of a sheet object, as well as it's name property (which is different) here...

The name property is what you see and change in the worksheet tab in Excel, but once again this is not the same as the object name. You can also change these things programmatically.

Answer 2

Try this:

Sub MacroMan()

    Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = _
        WorksheetFunction.VLookup("Rec", Sheet1.Range("C2:H25"), 2, False)

End Sub

The Range("B" & Rows.Count).End(xlUp) command is the equivalent of going to the last cell in column B and pressing Ctrl + ↑

We then use .Offset(1, 0) to get the cell after this (the next blank one) and write the value of your vlookup directly into this cell.

Answer 3

If Both work, then good, you have two working subs and you want to integrate them. You probably want to keep them so they might be useful for some other work later. Integrating them means invoking them in some third routine.

For many reasons, it is surely better and advised to avoid as much as possible to use (select, copy, paste) in VBA, and to use rather a direct copying method (range1.copy range2).

You need to make your routines as functions that return ranges objects, then in some third routine, invoke them

Function total_gsv() as range
    Dim movement_type_code As Variant:  movement_type_code = "Rec"
    Set total_gsv = Application.WorksheetFunction.VLookup(movement_type_code,Sheet1.Range("C2:H25"), 2, False)
End Sub

Function FindFirstBlankCell() as Range
    Dim Sheet2 As Worksheet: Set Sheet2 = ActiveSheet
    For Each cell In Sheet2.Columns(2).Cells
      If IsEmpty(cell) Then Set FindFirstBlankCell= cell: exit For
    Next cell
End Sub

Sub FindAndMoveGsv()
       total_gsv.copy FindFirstBlankCell
   ... 'some other work 
End Sub