Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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

Assume a table on "Sheet1" as follows.

A B

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

highlighted.

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

Try

myValue = Application.WorksheetFunction.VLookup(0.6,

Sheets("Sheet1").Range("A1:B3"), 2, False)

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.

I.e.

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

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.

BTW

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".

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.

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.

CreateObject("Scripting.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.

Related Topics:

- using vlookup in userform
- Vlookup in vba when lookup fails
- VLOOKUP in VBA
- Newbie VLOOKUP with dates question
- Help needed in VLOOKUP
- vlookup and error
- vlookup and the value #N/A
- Problem with VLookup in VBA
- VBA Vlookup returning wrong values
- VLookup in VBA
- Vlookup in VBA
- Help required in Vlookup
- How to make WorksheetFunction.MATCH OR .VLOOKUP work?
- vlookup with two imputs
- Vlookup returns #NA
- Perform a function based on a VLookup
- vlookup with two imputs
- Vlookup between two sheets with in a workbook to pick latest value via VBA
- Macro Macros and Increasing VBA Password Security Questions
- Macro's In Excel
- How to Make a Unsigned macro to Signed Macro
- Removing a vba module or macro with another macro
- Removing macro's shortcut key
- filter in a macro using a textbox value
- Re-alignment of address list in Excell using macro