How find character position # in string from right end? Or how get range row num
 

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.

How find character position # in string from right end? Or how get range row num
 

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.