Vllookup VBA Error

[querylal]

When i try below code it gives me error. "Unable to get the Vlookup property of the worksheetFunction class".

However when i copy paste vlookup formula used in VBA code in excell manually & change Cell(Z,2) value with B2 , then i gives right output. However when i keep Cell(2,2), it gives me error (N#A).

Sub ThreshXhigh()
Dim z As Integer
z = 2
While Cells(z, 2).Value <> ""


Cells(z, 19) = Application.WorksheetFunction.VLookup(Cells(z, 2), Master1, 19, False)


z = z + 1
Wend





End Sub

 

[alansidman]

Try replacing cells(z,2) in the vlookup function with Range("B" & z).

 

[Vbagreenhorn1]

Kick out the worksheetfunction and refer to the sheet the range is placed.

Sub ThreshXhigh()
 Dim z As Integer
 z = 2
 While Cells(z, 2).Value <> ""
    
    Cells(z, 19) = Application.VLookup(Cells(z, 2), Sheet2.Range("Master1"), 19, False)

     z = z + 1
 Wend
 End Sub

 

[meytithveasna]

try
Dim z As Integer
z = 2
While Cells("B"&z).Value <> ""


Cells("S"&z) = Application.WorksheetFunction.VLookup(Cells("B"&z),Range(" Master1"), 19, False)


z = z + 1
Wend

 

[MARK858]

@ meytithveasna, Cells("B"&z) is the incorrect syntax for Cells.
The syntax for Cells is Cells(Row number, Column number or column letter).

So either Cells(2, 2) or Cells(2, "B") are allowed but not Cells("B"& z). Range ("B" & z) would be allowed although none of these is a solution to the OP's issue.

https://support.microsoft.com/en-us...ges-by-using-visual-basic-procedures-in-excel

 

[meytithveasna]

Sorry yes it is my typo change cells to range

 

[MARK858]

@ querylal, what is Master1 in the formula?

 

[querylal]

@ querylal, what is Master1 in the formula?

Master1 is table i am creating by calling procedure. And when i run code one by one it creates master table. It is just something to do with refering source cell column


Sub DefineTable1()
'
' DefineTable Macro
'


'
Sheets("ThreshServingLow.xlsx").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
ActiveWorkbook.Names.Add Name:="Master1", RefersToR1C1:= _
"=ThreshServingLow.xlsx!R1C2:R5563C21"
End Sub

 

[MARK858]

That's a named range not a table and what is the name of the sheet that Cell(Z,2) is on?

 

[querylal]

MasterSheet.xlsx