Application.WorksheetFunction.Match

[TK]

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]

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

[TK]

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!