Vlookup Loop in vba, How to debug this?

Question

I have a pile of sheets with sheet name from 1 to 200(for example), and I want to create an overview. I used vlookup function in vba in a loop form. But there is a bug I cannot understand.

Sub Loop_Vlookup()
    Dim for_col As Long, i As Long, r As Long, c As Long, column As Long, ws As Long
    r = 3: c = 7: column = 2

    For for_col = 1 To Range("XFD2").End(xlToLeft).column - 6    
        ws = ActiveWorkbook.Sheets.Count - 2
        For i = 1 To ws
           Cells(r, c).Value = ActiveWorkbook.Application.WorksheetFunction.VLookup(Cells(1, c).Value, ActiveWorkbook.Sheets(i).Range("A:B"), column, 0)
           r = r + 1          
        Next
        r = 3
        c = c + 1    
    Next    
End Sub

I got this "Unable to get vlookup property of the Worksheet Function Class". I don't know the reason.

Answer 1

Instead of WorksheetFunction.Vlookup, you can use Application.Vlookup.

How to error handle 1004 Error with WorksheetFunction.VLookup?