Instrrev function
 

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

Instrrev function
 

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

--
Don Guillett
SalesAid Software

"DavidC" wrote in message
...
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

Instrrev function
 

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

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"DavidC" wrote in message
...
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

Instrrev function
 

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

--
Regards,
Tom Ogilvy


"DavidC" wrote in message
...
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

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"DavidC" wrote in message
...
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

Instrrev function
 

Thanks a heap Tom.

Learn something new everyday.

Regards

DavidC

"Tom Ogilvy" wrote:



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

--
Regards,
Tom Ogilvy


"DavidC" wrote in message
...
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

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"DavidC" wrote in message
...
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