Instrrev function

[Guest]

Hi,

Hope this is noted by Microsoft.

I have been using the Instrrev function in my code and could not figure out
why I was getting the 'wrong' answer. Well I was reading the description in
help too literally. The description states
"Returns the position of an occurrence of one string within another, from
the end of string." So my understanding was that it counted the number of
characters back from the end of the string and gave a number equal to the
number of characters back from the end of the string. In fact what the
function seems to do is to search the string from the end of the string, and
give the number of characters where the particular character occurs from the
START of the string. This 'error' gave me a few minutes of figuring out.

My suggestion to Microsoft is to make the descriptor a little more obvious
and state that the SEARCH starts from the end and it returns the number of
characters from the START of the string.

Regards

DavidC

 

[Don Guillett]

try this with/wo the +1
MsgBox Len(ActiveCell) - InStrRev(ActiveCell, "b") + 1

 

[Guest]

Hi Don,

Tried that and it proves the point. Where I had the difficulty was in
retuning the clomun reference not as an index but as the actual 'name' from
an address. The problem arises when as the column name can be one character
(A-Z) then goes totwo characters (AA-etc). I was using the intersect
function to find a value for a particular date. One of the ranegs then is
the column in which the relevant date is found. The date could be in any
column from A- the end column, and the address for the cell having the date
in it returns as $A$11. I realise the easiest way in this instance would be
simply to remove the last three characters as I am always using the same row,
but I wnated to make the code more flexible. So to get the column reference
for the range value,I had to strip off every other character from the last
'$', leaving only the absolute column reference. I tried using the column
reference address.column, but that returns the index value which range cannot
use in the intersect function.

Thanks though for the comment.

Regards

DavidC

 

[Tom Ogilvy]

s = Left(rng.Address(0,0), 2 + (rng.column < 27))

demo'd from the immediate window:

set rng = range("M3")
? Left(rng.Address(0,0), 2 + (rng.column < 27))
M
set rng = range("IV65536")
? Left(rng.Address(0,0), 2 + (rng.column < 27))
IV

 

[Guest]

Thanks a heap Tom.

Learn something new everyday.

Regards

DavidC