External file VBA Vlookup code

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

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


"David Travers" wrote:

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

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

David Travers wrote:
>
> 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

--

Dave Peterson