VBA VLOOKUP error - expected list separator or )

[mcchase]

I am getting this error for the vlookup below, where ":" is highlighted. How do I fix this? Thanks

x = VLookup(valvekey, model_table!A1:C3500, 2, False)

 

[Joe4]

Welcome to the Board!

VLOOKUP is a worksheet function, not a VBA one. So you cannot use it in VBA like that.
This link shows you how you can use it in VBA: Vlookup in Excel VBA

 

[mcchase]

Welcome to the Board!

VLOOKUP is a worksheet function, not a VBA one. So you cannot use it in VBA like that.
This link shows you how you can use it in VBA: Vlookup in Excel VBA

I didn't know that. Right now I have data on multiple sheets, say sheets A,B,C. The user hits a button on the main sheet, which triggers VBA to go thru sheets A,B,C to find a match in the 1st column of those sheets with data in the price sheet. This is proving to be time-consuming, as there could be 20-30 values in each sheet (A,B,C) which must be found in the price sheet. I thought vlookup would be quicker than executing an IF loop multiple times.

 

[Joe4]

I didn't know that. Right now I have data on multiple sheets, say sheets A,B,C. The user hits a button on the main sheet, which triggers VBA to go thru sheets A,B,C to find a match in the 1st column of those sheets with data in the price sheet. This is proving to be time-consuming, as there could be 20-30 values in each sheet (A,B,C) which must be found in the price sheet. I thought vlookup would be quicker than executing an IF loop multiple times.

Note that I am NOT saying you cannot use VLOOKUP in VBA. I am just saying your syntax for using it is not correct, as it the function does not natively exist in VBA.
But you can call many worksheet functions in VBA by prefacing it with "WorksheetFunction".
So you just have to change your syntax to reference it correctly.

Take a look at that link in detail. It shows you how to do that.

 

[mcchase]

I looked at the link and another one. I added WORKSHEETfUNCTION and RANGE, and debug says it's ok, but now I'm getting Rin-time error '424'. I looked-up the message but don't see what's wrong. Can you help with this?

xx = WorksheetFunction.VLookup(valvekey, model_table.Range("A1:C3500"), 2, False)

 

[Joe4]

Is "model_table" the name of a sheet or a table?

 

[mcchase]

"model_table" is a sheet

 

[Joe4]

OK, assuming the "valvekey" is a valid variable in VBA with a value, then try something like this:

Dim rng as Range
Set rng=Sheets("model_table").Range("A1:C3500")
xx = WorksheetFunction.VLookup(valvekey, rng, 2, False)

 

[mcchase]

now I'm getting "unable to get the property of the worksheetfunction class". Looking around could it be that valvekey is Variant/String and column A in model_table is General??? I did add a row in model_table containing "A" and tried the vlookup where valvekey = "A" and still got same error...I thought the error was because it couldn't find the original valvekey.

 

[Joe4]

From VBA, if you go to the Tools menu and select References, which entries to you see selected?
Do you see these four?

Also, what is the name of the module that you have placed the VBA code in?
Have you placed it in a new, general module, or in a Sheet module?
Please put in in a General module.