MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

VLOOKUP Use in Macros

  Asked By: Rani    Date: Dec 12    Category: MS Office    Views: 16803

I want to use the VLOOKUP function in a Excel 2003 macro.

Assume a table on "Sheet1" as follows.

1 0.45 22
2 0.6 10
3 1.0 15

My macro is as follows.

Sub Macro()
myValue = VLOOKUP(0.6,Sheet1!A1:B3,2,FALSE)
End Sub

I would expect "myValue" to be "10" after it is run. The problem is
it won't compile. The error message is "Compile error; Expected:
list separator or )" and has the ":" in my VLOOKUP equation

Macros don't come much simplier than this! So why won't it run?



5 Answers Found

Answer #1    Answered By: Jay Brown     Answered On: Dec 12

myValue = Application.WorksheetFunction.VLookup(0.6,
Sheets("Sheet1").Range("A1:B3"), 2, False)

Answer #2    Answered By: Rae Fischer     Answered On: Dec 12

Basically three separate problems.

(1) Sheet1!A1:B3 Is not a valid thing to put in a function  call parameter.
It's going to need quotes around it.

(2) The second parameter actually needs to be a range reference, so you'll
need something like a Range call to convert it.

(3) You need to prefix Excel functions with WorksheetFunction to tell Excel
that they're not local functions.


myValue = WorksheetFunction.VLookup(0.6, Range("Sheet1!A1:B3"), 2, False)

Answer #3    Answered By: Harriet Ward     Answered On: Dec 12

One more thing, Error trapping.

If the VLOOKUP fails and returns an error [#N/A, #REF, etc.], you may
need something like the ISERROR or ISERR functions as well.

I wondered if this was possible, but... I wanted a VLOOKUP, so I put
the VLOOKUP and the Table in a Data holding Sheet. Using named
Ranges, the Macro puts the "looked-up" value in a cell and gets the
response from the VLOOKUP cell. An ISERROR function  around the
VLOOKUP flags any error for "gracefulability".

Answer #4    Answered By: Darwishi Massri     Answered On: Dec 12

My thanks to all who replied. Just examining your approaches has been
educational helping to clarify my thinking with respect to how Excel
macros function  in general.

In particular, this example serves to illustrate the difference
between using a function such as VLOOKUP in a spreadsheet verses within
a macro.

Answer #5    Answered By: Binge Fischer     Answered On: Dec 12

Something else you may want to consider (since we're learning new
approaches here).

I've got a case where I have nearly 20,000 rows (ok, it's only
19,687) and have to lookup values from a report that contains over
70,000 rows.
Now, back when the table I'm reading from was only 60,000 rows, I
used Vlookups. The calculate function  at times took several minutes.
On top of that, I had to add a column to the table that concatenated
two cells in each row (requisition and item number) so that I then
could concatenate the same fields in the Data table for the vlookup.

With the help of a friend, I discovered the wonder of the Dictionary.

Let's say, I needed to Look Up three different columns.
I first created three Dictionaries, read in the data and load the
dictionaries using the requisition & item Number as the key field.

Then instead of a vlookup, I just look up the entry in the dictionary.

This made my overall report generation time go from 43 minutes down
to 14 !!!

Just something to think about...

Didn't find what you were looking for? Find more on VLOOKUP Use in Macros Or get search suggestion and latest updates.