Hi Folks:

I have been scratching my head over this problem for some time.

perhaps someone out there can help:

I want to use VLOOKUP in the following way:

the look up value is in 1st column of one spreadsheet and the table_array
is range A:B of another spreadsheet which is unsaved. How do I reference
the table array when I dont know the name of the workbook?

I know that the unsaved workbook is the last one opened so ive tried

set myworkbook=workbooks(wokrbooks.count)

then in my VLOOKUP formula
=VLOOKUP(A:A,[myworkbook]CA!$A:$B,2)

but that doesnt work

ive also tried

Set myrange = myworkbook.Worksheets("mysheet").Range("$A:$B")


=VLOOKUP(A:A,myrange,2)


and that does not work either

any help would be really appreciated


thanks

set myworkbook=workbooks(wokrbooks.count)


Does this give you the right workbook? Is it the last workbook opened by
the user, or opened by your code?

then in my VLOOKUP formula
=VLOOKUP(A:A,[myworkbook]CA!$A:$B,2)


Are you setting this formula in code? If so, the formula is a string and
you can build that string. If the formula looks like this

=VLOOKUP(A:A,[MyBook.xls]CA!$A:$B,2)

then your VBA would look like

Range("a1").Formula = "=VLOOKUP(A:A,[" & myworkbook.Name & "]CA!$A:$B,2)"


--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.