External file VBA Vlookup code

[David Travers]

I have a function in Excel on a worksheet to go and lookup the value in a
table in another file e.g

=VLOOKUP("TEST",'C:\Blairs[tables.xls]Sheet1'!$A$1:$C$100,2)

and this works fine.

However I'm trying to replicate the same in a VBA macro to create a static
document without any functions and cannot get the routine to work e.g

lookup_row_value = "TEST"
range_lookup = "'C:\[tables.xls]Sheet1'!$A$1:$C$100"
found_value = Application.WorksheetFunction.VLookup(lookup_row_value,
Range(range_lookup), 2, False)

Any ideas what I'm doing wrong and how I can fix it. Keep getting a Range
error.

Cheers

 

[Guest]

David

could this just be a typo?

In your working formula you refer to:
.....'C:\Blairs[tables.xls]Sheet1'....

and yet in your macro version you refer to:
.....'C:\[tables.xls]Sheet1'.....

You seem to be going easy on the Blairs!!

Giles

 

[Dave Peterson]

dim Range_Lookup as range
dim lookup_row_value as string
dim found_value as variant

lookup_row_value = "Test"

'tables.xls must be open
set range_lookup _
= workbooks("tables.xls").worksheets("sheet1").range("a1:c100")

found_value = application.vlookup(lookup_row_value, range_lookup, 2, false)

if iserror(found_value) then
'n/a would have been returned
else
'no error
end if

I have a function in Excel on a worksheet to go and lookup the value in a
table in another file e.g

=VLOOKUP("TEST",'C:\Blairs[tables.xls]Sheet1'!$A$1:$C$100,2)

and this works fine.

However I'm trying to replicate the same in a VBA macro to create a static
document without any functions and cannot get the routine to work e.g

lookup_row_value = "TEST"
range_lookup = "'C:\[tables.xls]Sheet1'!$A$1:$C$100"
found_value = Application.WorksheetFunction.VLookup(lookup_row_value,
Range(range_lookup), 2, False)

Any ideas what I'm doing wrong and how I can fix it. Keep getting a Range
error.

Cheers