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)

 

[mcchase]

All 4 under References are checked, as is MS Forms 2.0 Object Library. Everything is under General.

 

[mcchase]

going back to your comment about vlookup being a worksheet function...I moved valvekey to a (A,1) in a new sheet, called SEARCH. Cell (B,1) has the vlookup. After doing the move, the correct price is now in (B,2). With the issues we're having, I'm going to work with this option on few more price lookups and see how long it takes to retrieve the prices. Must be faster than looping thru about 3500 rows each time

 

[Joe4]

Since you are using 365, have you though about using the VSTACK function in conjunction with the new FILTER function?
That might be the fastest way.

See: FILTER Function
and How to use Microsoft Excel’s VSTACK() function to combine multiple data sets

 

[mcchase]

I thought I replied, but I don't see it. You said I can't use vlookup in VBA, so I put valvekey in (A,1) of a new sheet, and put the vlookup in ((B,1). Now I get the correct price in (B,1). I just need to get it the numerical equivalent of the vlookup value so I can add it to another number.

 

[mcchase]

guess I should have refreshed.

 

[Joe4]

You said I can't use vlookup in VBA

No, that is NOT what I said. And I already explained that. You should read my replies a little more carefully.

Is there some reason to use VBA and not Excel functions?
If you are truly looking for the most efficient solution, VSTACK and FILTER functions may be the way to go.
Did you look at those links?

Now I get the correct price in (B,1). I just need to get it the numerical equivalent of the vlookup value so I can add it to another number.

This is very confusing without seeing your data. If you are still having issues with this, please show us an actual example of the data you are working with and show us exactly what you are trying to do.
Note that when using VLOOKUP or any other matching functions, the data types of the values you are matching on must be the same.
You cannot compare "numbers" to "numbers entered as text", only "number to numbers" and "text to text".

 

[mcchase]

guess I should have refreshed.

I updated 1 part of my VB program, and it runs a lot faster using vlookup in a new sheet...thanks for your comments and responding so quickly.