return value from MATCH
 

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


---
Message posted from http://www.ExcelForum.com/

return value from MATCH
 

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 <" wrote:

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

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

return value from MATCH
 

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

:(


---
Message posted from http://www.ExcelForum.com/

return value from MATCH
 

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

return value from MATCH
 

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

return value from MATCH
 

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

--
Message posted from http://www.ExcelForum.com

return value from MATCH
 

What part are you specifying?

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



"pabs <" wrote:

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

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

return value from MATCH
 

perfect!

exaclty what I needed.
thanks

Pabs


---
Message posted from http://www.ExcelForum.com/

return value from MATCH
 

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.

pabs wrote in message ...
perfect!

exaclty what I needed.
thanks

Pabs


---
Message posted from http://www.ExcelForum.com/