Select one word of a string through each loop 1

[redaccess]

MyString = "Bill, Jane, Fred, Frank"

I want a loop that will pull the name Bill first, then on the second loop pull Jane, then on the third loop pull Fred ect.ect.

The string will always be comma delimited.

 

[Kryzsoccer]

this may not be what you had in mind but it is a work around that may work for you.

Open "C:\string.txt" For Output As #1
Print #1, mystring
Close #1
Open "C:\string.txt" For Input As #1
Do While Not EOF(1)
Input #1, name(x)
x = x + 1
Loop
Close #1

It creates a text file with "mystring" then it inputs each section(seperated by the commas) into name(x). To call each one simply use name(0) or whichever number you want.
Hope this helps!
Let me know how it goes!

 

[rmikesmith]

Kryzsoccer,

That's pretty clever!

redaccess: Here's my 2¢

Option Explicit
Option Base 1

Sub Main()
' This sub just demo's how you would call ParseString
Dim NameArr() As String
Dim i As Integer

  ParseString "Bill, Jane, Fred, Frank", NameArr
  For i = LBound(NameArr) To UBound(NameArr)
    Debug.Print NameArr(i)
  Next i
End Sub


Sub ParseString(ByVal MyString As String, ByRef Arr() As String)
Dim count As Integer
Dim tmpStr As String
Dim Pos As Integer

  count = 0
  Pos = InStr(1, MyString, ",", vbTextCompare)
  Do While Pos > 0
    count = count + 1
    ReDim Preserve Arr(count)
    Arr(count) = LTrim(Left$(MyString, Pos - 1))
    MyString = Mid$(MyString, Pos + 1)
    Pos = InStr(1, MyString, ",", vbTextCompare)
  Loop
  ReDim Preserve Arr(count + 1)
  Arr(count + 1) = LTrim(MyString)
  
End Sub

The ParseString procedure returns the individual names in an array, which you can then use for further processing.

Regards,
Mike

 

[sfvb]

Yet another way:

Dim arrString As Variant

mystring = "Bill, Jane, Fred, Frank"
arrString = Split(mystring, ",&quot

For idx = 0 To UBound(arrString)
MsgBox arrString(idx)
Next idx

 

[rmikesmith]

sfvb,

It can't get much simpler than that!!

Regards,
Mike

 

[RobBroekhuis]

sfvb,
That's a nifty function! Didn't know about that one. Is there a way to have it deal with variable numbers of spaces in between the list elements? I couldn't find one, and that would make the function even more valuable for me.
Cheers
Rob

 

[sfvb]

Rob,

If you're talking about the leading and/or trailing spaces, then you can use LTrim, RTrim, or Trim function (depending on what you want) inside the loop.


Dim arrString As Variant

mystring = "Bill, Jane,Fred , Frank "
arrString = Split(mystring, ",&quot

For idx = 0 To UBound(arrString)
arrString(idx) = Trim(arrString(idx))
MsgBox arrString(idx)
Next idx