| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find first o from right
HI all I have a column of numbers that includes o such as 0045a
078 009. Is there are formula that enables me to find the first 0 from the right. Thanks for all your help on this and previuos requests. -- delmac |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
find first o from right
Not a formula, but a procedure.
Sub find() Dim myPos As Long Dim myrng As String myrng = Range("A1").Value myPos = InStrRev(myrng, "0") End Sub This returns 10 as the position of the last 0 in your example. Mike F "delmac" wrote in message ... HI all I have a column of numbers that includes o such as 0045a 078 009. Is there are formula that enables me to find the first 0 from the right. Thanks for all your help on this and previuos requests. -- delmac |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
find first o from right
=SEARCH("0";ByteReverse(A1))
where cellA1 contains the number to be searched. The ByteReverse UDF: Function ByteReverse(InputString As String) As String Dim i As Long Dim ByteStr, ResultStr As String ResultStr = "" For i = Len(InputString) To 1 Step -1 ByteStr = Mid(InputString, i, 1) ResultStr = ResultStr & ByteStr Next i ByteReverse = ResultStr End Function Regards, Stefi €˛delmac€¯ ezt Ć*rta: HI all I have a column of numbers that includes o such as 0045a 078 009. Is there are formula that enables me to find the first 0 from the right. Thanks for all your help on this and previuos requests. -- delmac |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
find first o from right
When I tried this I got this message, it couldn't find the UDF how do I get
round this. Thanks again for your help =SEARCH("0",ByteReverse(D2)) #NAME? -- delmac "Stefi" wrote: =SEARCH("0";ByteReverse(A1)) where cellA1 contains the number to be searched. The ByteReverse UDF: Function ByteReverse(InputString As String) As String Dim i As Long Dim ByteStr, ResultStr As String ResultStr = "" For i = Len(InputString) To 1 Step -1 ByteStr = Mid(InputString, i, 1) ResultStr = ResultStr & ByteStr Next i ByteReverse = ResultStr End Function Regards, Stefi €˛delmac€¯ ezt Ć*rta: HI all I have a column of numbers that includes o such as 0045a 078 009. Is there are formula that enables me to find the first 0 from the right. Thanks for all your help on this and previuos requests. -- delmac |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
find first o from right
Hi Delmac,
First: I forgot to declare it Public: Public Function ByteReverse(InputString As String) As String Second: Where did you place the function code? It should be in a normal module? Stefi €˛delmac€¯ ezt Ć*rta: When I tried this I got this message, it couldn't find the UDF how do I get round this. Thanks again for your help =SEARCH("0",ByteReverse(D2)) #NAME? -- delmac "Stefi" wrote: =SEARCH("0";ByteReverse(A1)) where cellA1 contains the number to be searched. The ByteReverse UDF: Function ByteReverse(InputString As String) As String Dim i As Long Dim ByteStr, ResultStr As String ResultStr = "" For i = Len(InputString) To 1 Step -1 ByteStr = Mid(InputString, i, 1) ResultStr = ResultStr & ByteStr Next i ByteReverse = ResultStr End Function Regards, Stefi €˛delmac€¯ ezt Ć*rta: HI all I have a column of numbers that includes o such as 0045a 078 009. Is there are formula that enables me to find the first 0 from the right. Thanks for all your help on this and previuos requests. -- delmac |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
find first o from right
HI Stefi, this is what i put in the module: when I use the
search(bytereverse)line a nothing happens at all: If I use the line Function ByteReverse(InputString As String) As String I cant get past the the 1st line when I step into the module: If I use the code below I can step through the moduke but nothing happens when I use the =SEARCH("0";ByteReverse(A1)) Am I missing anything at all: Thanks for all your help so far. Public Function ByteReverse() As String Dim i As Long Dim ByteStr, ResultStr As String ResultStr = "" For i = Len(InputString) To 1 Step -1 ByteStr = Mid(InputString, i, 1) ResultStr = ResultStr & ByteStr Next i ByteReverse = ResultStr End Function -- delmac "Stefi" wrote: Hi Delmac, First: I forgot to declare it Public: Public Function ByteReverse(InputString As String) As String Second: Where did you place the function code? It should be in a normal module? Stefi €˛delmac€¯ ezt Ć*rta: When I tried this I got this message, it couldn't find the UDF how do I get round this. Thanks again for your help =SEARCH("0",ByteReverse(D2)) #NAME? -- delmac "Stefi" wrote: =SEARCH("0";ByteReverse(A1)) where cellA1 contains the number to be searched. The ByteReverse UDF: Function ByteReverse(InputString As String) As String Dim i As Long Dim ByteStr, ResultStr As String ResultStr = "" For i = Len(InputString) To 1 Step -1 ByteStr = Mid(InputString, i, 1) ResultStr = ResultStr & ByteStr Next i ByteReverse = ResultStr End Function Regards, Stefi €˛delmac€¯ ezt Ć*rta: HI all I have a column of numbers that includes o such as 0045a 078 009. Is there are formula that enables me to find the first 0 from the right. Thanks for all your help on this and previuos requests. -- delmac |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
find first o from right
Hi Delmac,
You shouldn't omit InputString from line Public Function ByteReverse(InputString As String) As String because it contains the string to be manipulated. What is the content of A1? If it doesn't contain "0" then it gives #VALUE! First try Function ByteReverse separately, and check if it really returns the characters in reverse order, e.g. if A1 contains JOHN, then =ByteReverse(A1) in say B1 should return NHOJ. Regards, Stefi €˛delmac€¯ ezt Ć*rta: HI Stefi, this is what i put in the module: when I use the search(bytereverse)line a nothing happens at all: If I use the line Function ByteReverse(InputString As String) As String I cant get past the the 1st line when I step into the module: If I use the code below I can step through the moduke but nothing happens when I use the =SEARCH("0";ByteReverse(A1)) Am I missing anything at all: Thanks for all your help so far. Public Function ByteReverse() As String Dim i As Long Dim ByteStr, ResultStr As String ResultStr = "" For i = Len(InputString) To 1 Step -1 ByteStr = Mid(InputString, i, 1) ResultStr = ResultStr & ByteStr Next i ByteReverse = ResultStr End Function -- delmac "Stefi" wrote: Hi Delmac, First: I forgot to declare it Public: Public Function ByteReverse(InputString As String) As String Second: Where did you place the function code? It should be in a normal module? Stefi €˛delmac€¯ ezt Ć*rta: When I tried this I got this message, it couldn't find the UDF how do I get round this. Thanks again for your help =SEARCH("0",ByteReverse(D2)) #NAME? -- delmac "Stefi" wrote: =SEARCH("0";ByteReverse(A1)) where cellA1 contains the number to be searched. The ByteReverse UDF: Function ByteReverse(InputString As String) As String Dim i As Long Dim ByteStr, ResultStr As String ResultStr = "" For i = Len(InputString) To 1 Step -1 ByteStr = Mid(InputString, i, 1) ResultStr = ResultStr & ByteStr Next i ByteReverse = ResultStr End Function Regards, Stefi €˛delmac€¯ ezt Ć*rta: HI all I have a column of numbers that includes o such as 0045a 078 009. Is there are formula that enables me to find the first 0 from the right. Thanks for all your help on this and previuos requests. -- delmac |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
find first o from right
Using the code below exactly does not work at all. I cant get by the 1st line
when I use the step-into function in the VB module. If I remove the (Input String) As String I can step into the module OK but nothing happens in the spreadsheet. Is there anywhere I have to declare A UDF , like function add-ins or something.Thanks again. Public Function ByteReverse(InputString) As String Dim InputString As String Dim i As Long Dim ByteStr, ResultStr As String ResultStr = "" For i = Len(InputString) To 1 Step -1 ByteStr = Mid(InputString, i, 1) ResultStr = ResultStr & ByteStr Next i ByteReverse = ResultStr End Functio -- delmac "Stefi" wrote: Hi Delmac, You shouldn't omit InputString from line Public Function ByteReverse(InputString As String) As String because it contains the string to be manipulated. What is the content of A1? If it doesn't contain "0" then it gives #VALUE! First try Function ByteReverse separately, and check if it really returns the characters in reverse order, e.g. if A1 contains JOHN, then =ByteReverse(A1) in say B1 should return NHOJ. Regards, Stefi €˛delmac€¯ ezt Ć*rta: HI Stefi, this is what i put in the module: when I use the search(bytereverse)line a nothing happens at all: If I use the line Function ByteReverse(InputString As String) As String I cant get past the the 1st line when I step into the module: If I use the code below I can step through the moduke but nothing happens when I use the =SEARCH("0";ByteReverse(A1)) Am I missing anything at all: Thanks for all your help so far. Public Function ByteReverse() As String Dim i As Long Dim ByteStr, ResultStr As String ResultStr = "" For i = Len(InputString) To 1 Step -1 ByteStr = Mid(InputString, i, 1) ResultStr = ResultStr & ByteStr Next i ByteReverse = ResultStr End Function -- delmac "Stefi" wrote: Hi Delmac, First: I forgot to declare it Public: Public Function ByteReverse(InputString As String) As String Second: Where did you place the function code? It should be in a normal module? Stefi €˛delmac€¯ ezt Ć*rta: When I tried this I got this message, it couldn't find the UDF how do I get round this. Thanks again for your help =SEARCH("0",ByteReverse(D2)) #NAME? -- delmac "Stefi" wrote: =SEARCH("0";ByteReverse(A1)) where cellA1 contains the number to be searched. The ByteReverse UDF: Function ByteReverse(InputString As String) As String Dim i As Long Dim ByteStr, ResultStr As String ResultStr = "" For i = Len(InputString) To 1 Step -1 ByteStr = Mid(InputString, i, 1) ResultStr = ResultStr & ByteStr Next i ByteReverse = ResultStr End Function Regards, Stefi €˛delmac€¯ ezt Ć*rta: HI all I have a column of numbers that includes o such as 0045a 078 009. Is there are formula that enables me to find the first 0 from the right. Thanks for all your help on this and previuos requests. -- delmac |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
find first o from right
Hi Delmac,
Public Function ByteReverse(InputString) As String Dim InputString As String OMIT THIS LINE Dim i As Long Dim ByteStr, ResultStr As String ResultStr = "" For i = Len(InputString) To 1 Step -1 ByteStr = Mid(InputString, i, 1) ResultStr = ResultStr & ByteStr Next i ByteReverse = ResultStr End Functio This code must work if you place it in a normal module: Public Function ByteReverse(InputString) As String Dim i As Long Dim ByteStr, ResultStr As String ResultStr = "" For i = Len(InputString) To 1 Step -1 ByteStr = Mid(InputString, i, 1) ResultStr = ResultStr & ByteStr Next i ByteReverse = ResultStr End Function If it doesn't work, post your workbook to me to this address: Regards, Stefi |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
| Despite data existing in Excel 2002 spreadsheet Find doesn't find | Excel Discussion (Misc queries) | |||
| find and delete duplicate entries in two columns or find and prin. | Excel Programming | |||
| find and delete text, find a 10-digit number and put it in a textbox | Excel Programming | |||
| backwards find function to find character in a string of text | Excel Programming | |||
Linear Mode
