error during Vlookup from worksheet A to worksheet

Question

highly appreciate your help on this. I'm trying to vlookup from worksheet A to worksheet B and i did find it the example code in this forum but suddenly got error - Run-time error '1004': Method 'Range' of object_Worksheet' failed

Sub Sample()
    Dim wbThis As Workbook, wbThat As Workbook
    Dim wsThis As Worksheet, wsThat As Worksheet
    Dim aCell As Range

    Set wbThis = ThisWorkbook
    '~~> Let's say this is the sheet where you want the result
    '~~> Change name as applicable
    Set wsThis = wbThis.Sheets("Sheet1")

    '~~> Change path as applicable
    Set wbThat = Workbooks.Open("C:\Users\Fahmi\Desktop\VBA TESTING\Template.xlsx")
    Set wsThat = wbThat.Sheets("CtyAccesCode")

    With wsThis
        For Each aCell In .Range("H2:H" & LastRow)


            If Len(Trim(.Range("AD" & aCell.Row).Value)) <> 0 Then
                .Cells(aCell.Row, 28) = Application.WorksheetFunction.VLookup( _
                                        aCell.Value, wsThat.Range("A1:B13"), 2, 0)
            End If
        Next aCell
    End With

    wbThat.Close (False)
End Sub

and the error on this line

thanks in advance!

The problem is that code never declares or assigns a value to variable `LastRow` so you're basically saying "For each cell in H2:H0" and because H0 isn't a valid cell reference, the code throws an error. — tigeravatar, Nov 03 '16 at 16:35

score 0 · Answer 1 · edited May 23 '17 at 12:00

You never declared lastRow. There are more elegant and bulletproof ways to do it, but a quick hack to keep your efforts going could be something like this:

Dim lastRow As Integer
lastRow = wsThis.UsedRange.Rows.Count

You can find more bulletproof ways of finding the actual last row here:

How can I find last row that contains data in the Excel sheet with a macro?

But depending on the contents of your worksheet, you might have simpler or more transparent ways.

error during Vlookup from worksheet A to worksheet

1 Answers1