CODEWITHSUNDEEP
excelvbavlookup
suupmaster
suupmaster

Reputation: 13

how to fix vlookup worksheet property error in VBA

I have the following code which when run, returns error 1004 "Unable to get Vlookup Property of the WorksheetFunction Class" on the line "If strF = Qualified". Please guide how to fix. Thanks in advance.

Sub Geo()

Dim i As Long
Dim strB As String, strF As String, strResult As String

For i = 1 To 100

With ActiveSheet
strB = .Cells(i, 2).Value2
strF = .Cells(i, 6).Value2
End With

If strB = vbNullString Then
strResult = vbNullString

Else
If strF = "Qualified" Then
strResult = Application.WorksheetFunction.VLookup(Range("B3"), Sheets("Pipeline Master").Range("B6:T3000"), 6, False)**

On Error Resume Next
strResult = Application.WorksheetFunction.VLookup(Range("B3"), Sheets("2019-Wins&Losses").Range("A7:M5000"), 2, False)

On Error Resume Next
strResult = Application.WorksheetFunction.VLookup(Range("B3"), Sheets("PipelineHistory").Range("C3:G15934"), 5, False)

On Error Resume Next

strResult = " "
On Error GoTo 0

End If
End If

ActiveSheet.Cells(i, 11).Value2 = strResult
Next i

End Sub

Upvotes: 0

Views: 95

Answers (1)

Johnny Abreu
Johnny Abreu

Reputation: 373

Why do you have two ** at the end of this code line strResult = Application.WorksheetFunction.VLookup(Range("B3"), Sheets("Pipeline Master").Range("B6:T3000"), 6, False) ? I tried it out and it worked fine.

edit: If it still doesn't work for you maybe try to use the .formula property instead.

With ThisWorkbook.Worksheets("Pipeline Master").Range("B3")
    .Formula = "=VLOOKUP(""Qualified"",B6:T3000,6,FALSE)"
    .Value = .Value
End With

Upvotes: 1

Related Questions