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

 

[querylal]

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

MasterSheet.xlsx

Mark when i put Colum B:B in vlookup formula manually ,it works . But when i put below formula it doesn't yeild any result. Your help will be appreciated.

Cells(Z, 19) = Application.WorksheetFunction.VLookup("B:B", Master1, 19, False)

 

[MARK858]

What do you get with

Sheets("MasterSheet.xlsx").Cells(z, 19) = Application.WorksheetFunction.VLookup(Sheets("MasterSheet.xlsx").Cells(z, 2), Sheets("ThreshServingLow.xlsx").Range("Master1"), 19, False)

I am assuming that Cells(z, 19) is on Sheets("MasterSheet.xlsx")

 

[querylal]

What do you get with

Sheets("MasterSheet.xlsx").Cells(z, 19) = Application.WorksheetFunction.VLookup(Sheets("MasterSheet.xlsx").Cells(z, 2), Sheets("ThreshServingLow.xlsx").Range("Master1"), 19, False)

I am assuming that Cells(z, 19) is on Sheets("MasterSheet.xlsx")

Yes cells(z, 19) is on same Mastersheet.xlsx.

When i tried your latest solution doesn't yeild result.

 

[MARK858]

When i tried your latest solution doesn't yeild result.

Doesn't yield result? are you saying that it doesn't err out?

 

[meytithveasna]

One way of vba for a worksheet formula is to enter the formula normally in the cell, turn on the macro recorder, select the cell, hit f2 & enter, then turn off the recorder. You don;t get the most nice code, but at least it works!

 

[MARK858]

One way of vba for a worksheet formula is to enter the formula normally in the cell, turn on the macro recorder, select the cell, hit f2 & enter, then turn off the recorder. You don;t get the most nice code, but at least it works!

If the objective is to put a formula in a cell that is what you would do but that is not always the objective (normally to resolve calculation time issues with the formula).

Neither Worksheetfunction or Application actually put the formula in the cell.

 

[meytithveasna]

try this
Range(z&"S").value = Application.VLookup(Range(z&"B"), Range("Master1"),19,False)

 

[querylal]

If the objective is to put a formula in a cell that is what you would do but that is not always the objective (normally to resolve calculation time issues with the formula).

Neither Worksheetfunction or Application actually put the formula in the cell.

Mark i figure out issue. Data what i making table of, has some kind of format for in between columns (Nokia format of putting number in row as text).

When i copy few column from required sheet in another sheet and make range out of it, than it works. So it has to do with format of column in between table i was selecting.

 

[querylal]

try this
Range(z&"S").value = Application.VLookup(Range(z&"B"), Range("Master1"),19,False)

Syntax Error

 

[MARK858]

When i copy few column from required sheet in another sheet and make range out of it, than it works. So it has to do with format of column in between table i was selecting.

Try selecting the offending column, click Data - Text to Columns - click Next twice - make sure General is checked and click Finish.

Now see if that has made a difference when you run the macro.