Thanks in advance.
I have a macro where I need to get the row number from a
range (like the 11 in "$G$11") becausen I will compare it
with another row number later (if rownum1 < rownum2
then ...)
The way I do it now is:
1 get the position of the first '$' from the right (I made
a function like InStr, except it looks from the right end
of string, not the left end)
2 get the right part of the string for as many chars as
(total length of string - position of first '$' from left)

ie for "$G$11":
1 position of first '$' from right is 3
2 length of string-position=row number length. 5-3=2, so
get "11"
Then I can compare "11" with another row number (like 6 or
whatever).
I am wondering if there is an easier way to do this, or is
there a built-in command for this? I wanted to use InStr,
but InStr looks for the first occurence of a string from
the left, not right. I can't use right(string1,2) either
because the row number might be "1" or "326" or "4355"
or "65029", where the number of digits change.
Any ideas?
Thanks again.

Ian,

In Excel 2000 and later, there is a function called InStrRev
which works like InStr but searches right to left. But I don't
think you need to do all the string manipulation you're doing.
If you have a string containing a valid address, just use the Row
property to return the row number. For example,

Dim S As String
Dim RowNum As Long
S = "$G$11"
RowNum = Range(S).Row
Debug.Print RowNum


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Ian Elliott" wrote in
message ...
Thanks in advance.
I have a macro where I need to get the row number from a
range (like the 11 in "$G$11") becausen I will compare it
with another row number later (if rownum1 < rownum2
then ...)
The way I do it now is:
1 get the position of the first '$' from the right (I made
a function like InStr, except it looks from the right end
of string, not the left end)
2 get the right part of the string for as many chars as
(total length of string - position of first '$' from left)

ie for "$G$11":
1 position of first '$' from right is 3
2 length of string-position=row number length. 5-3=2, so
get "11"
Then I can compare "11" with another row number (like 6 or
whatever).
I am wondering if there is an easier way to do this, or is
there a built-in command for this? I wanted to use InStr,
but InStr looks for the first occurence of a string from
the left, not right. I can't use right(string1,2) either
because the row number might be "1" or "326" or "4355"
or "65029", where the number of digits change.
Any ideas?
Thanks again.