Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA mid() function
Using XP & Excel2002 to find a string in another string, gets me weird results:
Dim S As String Dim L As Long S = "0123456789" For L = 1 To 10 Debug.Print Mid$(S, L, L) Next The above code returns the correct value only on the 1st iteration. Thereafter the Mid$ keeps growing. Is there an API procedure to accomplish this? Can't seem to find it. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA mid() function
change
Debug.Print Mid$(S, L, L) to Debug.Print Mid$(S, L, 1) at least that's what I assume you want! Regards, Peter T "vello" wrote in message ... Using XP & Excel2002 to find a string in another string, gets me weird results: Dim S As String Dim L As Long S = "0123456789" For L = 1 To 10 Debug.Print Mid$(S, L, L) Next The above code returns the correct value only on the 1st iteration. Thereafter the Mid$ keeps growing. Is there an API procedure to accomplish this? Can't seem to find it. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA mid() function
If you are looking to find a string in another string use INSTR()..Check out
the help on VBA InStr() If this post helps click Yes --------------- Jacob Skaria "vello" wrote: Using XP & Excel2002 to find a string in another string, gets me weird results: Dim S As String Dim L As Long S = "0123456789" For L = 1 To 10 Debug.Print Mid$(S, L, L) Next The above code returns the correct value only on the 1st iteration. Thereafter the Mid$ keeps growing. Is there an API procedure to accomplish this? Can't seem to find it. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA mid() function
I'm not sure what your trying to do but the code will return
0 12 234 3456 45678 56789 6789 789 89 9 The reason it does that is for every loop L increases by 1 and your using L as both start position and lengthe of text to return so in (say) the 4th iteration of the loop your formula is Debug.Print Mid$("0123456789", 4, 4) From this you should be able to work out why the returned string gets shorter when L=6 Perhaps what you really want is Debug.Print Mid$(S, L, 1) Mike "vello" wrote: Using XP & Excel2002 to find a string in another string, gets me weird results: Dim S As String Dim L As Long S = "0123456789" For L = 1 To 10 Debug.Print Mid$(S, L, L) Next The above code returns the correct value only on the 1st iteration. Thereafter the Mid$ keeps growing. Is there an API procedure to accomplish this? Can't seem to find it. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA mid() function
Your posting is not very clear. You say you want to "find a string in
another string", but the code you posted doesn't even come close to doing that. Exactly what are you trying to do? Try to give an example that demonstrates whatever it is. -- Rick (MVP - Excel) "vello" wrote in message ... Using XP & Excel2002 to find a string in another string, gets me weird results: Dim S As String Dim L As Long S = "0123456789" For L = 1 To 10 Debug.Print Mid$(S, L, L) Next The above code returns the correct value only on the 1st iteration. Thereafter the Mid$ keeps growing. Is there an API procedure to accomplish this? Can't seem to find it. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA mid() function
Debug.Print Mid$(S, L, 1)
Use a 1 not "L" "vello" wrote: Using XP & Excel2002 to find a string in another string, gets me weird results: Dim S As String Dim L As Long S = "0123456789" For L = 1 To 10 Debug.Print Mid$(S, L, L) Next The above code returns the correct value only on the 1st iteration. Thereafter the Mid$ keeps growing. Is there an API procedure to accomplish this? Can't seem to find it. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA mid() function
I'm not "picking" on your Charlie, it's just I needed to attach this note to
either you message or Peter T's message... I chose yours. Both you and Peter made the same suggestion (to change the second L to a one)... my question to the both of you is... How does that explain the OP's statement "The above code returns the correct value only on the 1st iteration"? It would seem that changing the second L to a one would make it so the first iteration would fail, not succeed. -- Rick (MVP - Excel) "Charlie" wrote in message ... Debug.Print Mid$(S, L, 1) Use a 1 not "L" "vello" wrote: Using XP & Excel2002 to find a string in another string, gets me weird results: Dim S As String Dim L As Long S = "0123456789" For L = 1 To 10 Debug.Print Mid$(S, L, L) Next The above code returns the correct value only on the 1st iteration. Thereafter the Mid$ keeps growing. Is there an API procedure to accomplish this? Can't seem to find it. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA mid() function
Hi Rick,
If you run the loop first with Mid$(S, L, L) then with Mid$(S, L, 1) I think your question is answered. That is, "assuming" the OP wants each character of the sample string returned individually. Regards, Peter T "Rick Rothstein" wrote in message ... I'm not "picking" on your Charlie, it's just I needed to attach this note to either you message or Peter T's message... I chose yours. Both you and Peter made the same suggestion (to change the second L to a one)... my question to the both of you is... How does that explain the OP's statement "The above code returns the correct value only on the 1st iteration"? It would seem that changing the second L to a one would make it so the first iteration would fail, not succeed. -- Rick (MVP - Excel) "Charlie" wrote in message ... Debug.Print Mid$(S, L, 1) Use a 1 not "L" "vello" wrote: Using XP & Excel2002 to find a string in another string, gets me weird results: Dim S As String Dim L As Long S = "0123456789" For L = 1 To 10 Debug.Print Mid$(S, L, L) Next The above code returns the correct value only on the 1st iteration. Thereafter the Mid$ keeps growing. Is there an API procedure to accomplish this? Can't seem to find it. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA mid() function
Uh... never mind... it just dawned on me how I was misreading the original
code. You are (probably) right.. the third argument should be a 1. -- Rick (MVP - Excel) "Peter T" <peter_t@discussions wrote in message ... Hi Rick, If you run the loop first with Mid$(S, L, L) then with Mid$(S, L, 1) I think your question is answered. That is, "assuming" the OP wants each character of the sample string returned individually. Regards, Peter T "Rick Rothstein" wrote in message ... I'm not "picking" on your Charlie, it's just I needed to attach this note to either you message or Peter T's message... I chose yours. Both you and Peter made the same suggestion (to change the second L to a one)... my question to the both of you is... How does that explain the OP's statement "The above code returns the correct value only on the 1st iteration"? It would seem that changing the second L to a one would make it so the first iteration would fail, not succeed. -- Rick (MVP - Excel) "Charlie" wrote in message ... Debug.Print Mid$(S, L, 1) Use a 1 not "L" "vello" wrote: Using XP & Excel2002 to find a string in another string, gets me weird results: Dim S As String Dim L As Long S = "0123456789" For L = 1 To 10 Debug.Print Mid$(S, L, L) Next The above code returns the correct value only on the 1st iteration. Thereafter the Mid$ keeps growing. Is there an API procedure to accomplish this? Can't seem to find it. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA mid() function
Thanks to all of you for your notes. Now I see where the problem was.
What I want to do is to find a certain 'word' in a textstream and then place it in an Excel cell. Seems to me there was an API that did this well, but can't remember it. "Rick Rothstein" wrote: Your posting is not very clear. You say you want to "find a string in another string", but the code you posted doesn't even come close to doing that. Exactly what are you trying to do? Try to give an example that demonstrates whatever it is. -- Rick (MVP - Excel) "vello" wrote in message ... Using XP & Excel2002 to find a string in another string, gets me weird results: Dim S As String Dim L As Long S = "0123456789" For L = 1 To 10 Debug.Print Mid$(S, L, L) Next The above code returns the correct value only on the 1st iteration. Thereafter the Mid$ keeps growing. Is there an API procedure to accomplish this? Can't seem to find it. . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA mid() function
Did I miss something? On the first iteration L would equal 1. The loop is
from 1 to 10 -- the STRING started at zero ("0123...") "Rick Rothstein" wrote: I'm not "picking" on your Charlie, it's just I needed to attach this note to either you message or Peter T's message... I chose yours. Both you and Peter made the same suggestion (to change the second L to a one)... my question to the both of you is... How does that explain the OP's statement "The above code returns the correct value only on the 1st iteration"? It would seem that changing the second L to a one would make it so the first iteration would fail, not succeed. -- Rick (MVP - Excel) "Charlie" wrote in message ... Debug.Print Mid$(S, L, 1) Use a 1 not "L" "vello" wrote: Using XP & Excel2002 to find a string in another string, gets me weird results: Dim S As String Dim L As Long S = "0123456789" For L = 1 To 10 Debug.Print Mid$(S, L, L) Next The above code returns the correct value only on the 1st iteration. Thereafter the Mid$ keeps growing. Is there an API procedure to accomplish this? Can't seem to find it. . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA mid() function
No, you didn't miss anything... I did. Sorry for the confusion, but
apparently I misread the original post and then made my comment based on my misreading. -- Rick (MVP - Excel) "Charlie" wrote in message ... Did I miss something? On the first iteration L would equal 1. The loop is from 1 to 10 -- the STRING started at zero ("0123...") "Rick Rothstein" wrote: I'm not "picking" on your Charlie, it's just I needed to attach this note to either you message or Peter T's message... I chose yours. Both you and Peter made the same suggestion (to change the second L to a one)... my question to the both of you is... How does that explain the OP's statement "The above code returns the correct value only on the 1st iteration"? It would seem that changing the second L to a one would make it so the first iteration would fail, not succeed. -- Rick (MVP - Excel) "Charlie" wrote in message ... Debug.Print Mid$(S, L, 1) Use a 1 not "L" "vello" wrote: Using XP & Excel2002 to find a string in another string, gets me weird results: Dim S As String Dim L As Long S = "0123456789" For L = 1 To 10 Debug.Print Mid$(S, L, L) Next The above code returns the correct value only on the 1st iteration. Thereafter the Mid$ keeps growing. Is there an API procedure to accomplish this? Can't seem to find it. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
User Function Question: Collect Condition in Dialog Box - But How toInsert into Function Equation? | Excel Programming | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Excel - User Defined Function Error: This function takes no argume | Excel Programming |