Is it possible to use a value from one spreadsheet as the lookup value
and the range from another spreadsheet as the range argument? If so,
what would the syntax be?

Thanks

Both workbooks are open?

dim myLookup as range
dim myLookupRng as range
dim res as variant

set mylookup = workbooks("book1.xls").worksheets("sheet1").range( "a1")
set mylookuprng = workbooks("book2.xls").worksheets("sheet99").range ("a1:A99")

res = application.match(mylookup.value, mylookuprng,0)

if iserror(res) then
msgbox "not found"
else
msgbox "Found on row: " & res
end if

is one way.


TK wrote:

Is it possible to use a value from one spreadsheet as the lookup value
and the range from another spreadsheet as the range argument? If so,
what would the syntax be?

Thanks

--

Dave Peterson

Dave Peterson wrote:
Both workbooks are open?

dim myLookup as range
dim myLookupRng as range
dim res as variant

set mylookup = workbooks("book1.xls").worksheets("sheet1").range( "a1")
set mylookuprng = workbooks("book2.xls").worksheets("sheet99").range ("a1:A99")

res = application.match(mylookup.value, mylookuprng,0)

if iserror(res) then
msgbox "not found"
else
msgbox "Found on row: " & res
end if

is one way.


TK wrote:

Is it possible to use a value from one spreadsheet as the lookup value
and the range from another spreadsheet as the range argument? If so,
what would the syntax be?

Thanks


Works great. Thanks a lot!