'vba vlookup does not work in loop

I am working on a tool to analyse data. The data to be analyzed is manually copied into the first sheet, then I am planning to run a macro which analyzes the data into the second sheet. I need the vlookup function for one property of each entry:

Worksheets("x1").Cells(z, 4).Value = Application.WorksheetFunction.VLookup(Worksheets("x1").Cells(z, 2).Value, Worksheets("x2").Range("$A$1:$B$23"), 2, False)

Obviously z is the the variable that indicates the current row (I do copy about 700 rows of data in a "for"-loop).

Whenever I try to execute the whole sub it returns a runtime error(1004). The strange thing about it is that whenever I use single steps to execute the macro it works perfectly fine.

Does anybody know whats goin on here?

Thanks in advance

Solution 1:^[1]

Here's some sample error handling code to get you going.

Sub TestVLookup()

    On Error GoTo ErrHandler
    For Z = 1 To 700
        Worksheets("x1").Cells(Z, 4).Value = Application.WorksheetFunction.VLookup(Worksheets("x1").Cells(Z, 2).Value, Worksheets("x2").Range("$A$1:$B$23"), 2, False)
    Next Z

Exit Sub

ErrHandler:
    Debug.Print "Z: " + CStr(Z) + "|" + CStr(Err.Number) + ": " + Err.Description

End Sub

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Cody ?

'vba vlookup does not work in loop

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]