Excel VBA VLookup Run-time error '1004'

Question

I want to consolidate two sheets. In Tabelle 3 is already some data. Therefore, I defined the Next Free Row (NFR) and want my data from Tabelle 5 added to the next free row in Tabelle 3. Therefore, I wrote the following VLookup function.

Sub ConsolidateData()

Dim lastrow As Long
Dim NFR As Long

lastrow = Tabelle5.Range("A" & Rows.Count).End(xlUp).Row
NFR = Tabelle3.Range("A" & Rows.Count).End(xlUp).Offset(-3).Row

Set myrange = Tabelle5.UsedRange


    For i = 4 To lastrow


        Tabelle3.Cells(NFR + i, 1) = Application.WorksheetFunction.VLookup(Tabelle5.Cells(i, 1), myrange, 1, False)


        Tabelle3.Cells(NFR + i, 2) = Application.WorksheetFunction.VLookup(Tabelle5.Cells(i, 1), myrange, 2, False)


    Next i

End Sub

Even though, I'm already using this code in a different workbook, where it works smooth, it doesn't work here. Instead Run-time error '1004' occurs for this line:

Tabelle3.Cells(NFR + i, 1) = Application.WorksheetFunction.VLookup(Tabelle5.Cells(i, 1), myrange, 1, False)

Does anyone see the mistake or can tell me what I've coded wrong?

@CFO I have to set i=4 because in Tabelle 5 my required data starts in row 4 . In case I'm transfering it like that to the NFR in Tabelle 3 there would be a gap of 3 rows. Therefore I Offset(-3) rows — HPM

TomJohnRiddle TomJohnRiddle · Accepted Answer · 2018-04-26T14:05:10

It seems that Vlookup cannot find value you are looking for and therefore throws an error. Application.WorksheetFunction.VLookup will return error '1004' if value cannot be found. Please consider following test:

Put in cell A2 value of 15.
Run below macros

macro 1:

Sub test1()

check = Application.WorksheetFunction.VLookup(15, Range("A1:A5"), 1, False)

Debug.Print check

End Sub

macro 2:

Sub test2()

check = Application.WorksheetFunction.VLookup(1, Range("A1:A5"), 1, False)

Debug.Print check

End Sub

As you can see second one throws an error. To overcome that issue you should change WorksheetFunction.VLoookup to Application.VLookup and implement error checking:

Sub test2()

If IsError(Application.VLookup(1, Range("A1:A5"), 1, False)) = False Then

check = Application.VLookup(1, Range("A1:A5"), 1, False)

End If

Debug.Print check

End Sub

Please look here as well: How to error handle 1004 Error with WorksheetFunction.VLookup?

Excel VBA VLookup Run-time error '1004'

1 Answers