Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
worksheet function for InStrRev() | Excel Programming | |||
Strange behavior of InStrRev() function | Excel Programming | |||
is there a instrRev function in excel functions? | Excel Worksheet Functions | |||
is there a instrRev function in excel functions? | Excel Worksheet Functions | |||
InStrRev function not working? | Excel Programming |