return value from MATCH

[pabs]

I'm trying to get the value returned from the following MATCH

=MATCH("some name",sheet1!A1:A450,0)

it will return the row number that the match occured in.
this is the value I need.
I need to retrieve it from a macro.

I can;t seem to figure out the syntax to get that value.

if I simply use in a worksheet ...the formula will return what I need
but and dump in the current cell. I don't want to dump the result in a
cell but simply in my variable frommy macro..
how would I go about this?

thanks

PAbs

 

[Dave Peterson]

dim res as variant

res = application.match("some name", worksheets("sheet1").range("a1:a450"),0)

if iserror(res) then
msgbox "no match!"
else
msgbox worksheets("sheet1").range("a1:a450")(res) & _
" is " & res & " rows into the table"
end if

 

[pabs]

that seems to works...
but how do I get the integer value form it,,
I need to pass that value as an integer to my other routine.

if I try to pass my variant "res" it gives me a "type mismatch"
error..

 

[Guest]

Pab

Are you trying to pass res or "res". If the latter, then you will not get the contents of the variable (ie the value of the match), but the alpha string res

Tony

 

[Robin Clay]

-----Original Message-----
if I try to pass my variant "res"
it gives me a "type mismatch"
error..

Try putting it in brackets -
that will transfer the value only, e.g.

Dim res as Variant, myVariable as Long

res = "1234"
myVariable = (res) + 46

RClay AT haswell DOT com

 

[pabs]

actually, that works...gettting the value in res.

my problem is that I need to refer to another file.

if you look at the formula it specifies a particular workbook
(sheet1)

res = application.match("some name", worksheets("*sheet1
").range("a1:a450"),0)


I also need to specify from what file it's in (since I have more tha
one file open at once)

I tried [fileName.xls]Sheet1 but that didn't work when I tried it...

not sure on what the syntax would have to be..

 

[Dave Peterson]

What part are you specifying?

maybe replace
worksheets("sheet1").range("a1:a450")
with
workbooks("filename.xls").worksheets("sheet975").range("a1:a450")

actually, that works...gettting the value in res.

my problem is that I need to refer to another file.

if you look at the formula it specifies a particular workbook.
(sheet1)

res = application.match("some name", worksheets("*sheet1*
").range("a1:a450"),0)

I also need to specify from what file it's in (since I have more than
one file open at once)

I tried [fileName.xls]Sheet1 but that didn't work when I tried it...

not sure on what the syntax would have to be...

 

[pabs]

perfect!

exaclty what I needed.
thanks

Pabs

 

[John Tjia]

Here is an alternative


Dim x as integer

x= ThisWorkbook.Worksheet("Sheet975").Range("A1:A450").Find("some
name",lookat:=xlWhole).Row

x is the row number you want.