Unable to get the Vlookup property of the worksheetfunction class error

Question

I am trying to run some macro with the following code but it shows me the message "Error 1004 Unable to get the Vlookup property of the worksheetfunction class error" ... how can I correct this error ?

Sub GenerateReport()
  Dim summary: Set summary = ThisWorkbook.Sheets("Summary")
  Dim logs As Worksheet: Set logs = ThisWorkbook.Sheets("Logs")
  
  Dim cell As Range, i As Long
  
   i = 3
  
  For Each cell In logs.Range("AA1:AA1000")
    If IsEmpty(cell.Value) = True Then
    
      Exit Sub
      
    ElseIf Not IsError(Application.Match(cell.Value, summary.Range("B2:B100"), 0)) Then
    
     GoTo nextIteration

    End If
    
    'adds unique login to report
    summary.Cells(i, 2) = cell.Value
    
    'counts number of times login appears on the log sheet
    summary.Cells(i, 4).Formula = "=COUNTIF(Logs!A3:A2500, B" & i & ")"
    
    
    summary.Cells(i, 1) = WorksheetFunction.VLookup(summary.Cells(i, 2), Sheet2.Range("A:G"), 2, False)
    summary.Cells(i, 3) = WorksheetFunction.VLookup(summary.Cells(i, 2), Sheet2.Range("A:G"), 5, False)    
    i = i + 1
    
nextIteration:       
  Next cell
End Sub

Check this solution by @VBasic2008 Sir, use `INDEX/MATCH` instead of `VLOOKUP` (VBA Formula) --> https://stackoverflow.com/a/71416247/8162520 — Mayukh Bhattacharya, Mar 11 '22 at 16:16
Actually, you can use the VLOOKUP method of the Application object, instead of the WorksheetFunction object. For example, `Application.VLookup(summary.Cells(i, 2), Sheet2.Range("A:G"), 2, False)`. — Domenic, Mar 11 '22 at 17:21
Are `logs` and `Sheet2` the same worksheets? What about this logic: you are trying to find a value in range `summary.Range("B2:B100")` and right after you are (over)writing to the same column with `summary.Cells(i, 2) = cell.Value`? Please do clarify. — VBasic2008, Mar 11 '22 at 19:18
Thank you both of you. I am going to try and keep you updated. Thank you. — David Knight, Mar 11 '22 at 20:35

Unable to get the Vlookup property of the worksheetfunction class error

0 Answers0