Here are a couple ways of looping through a string, or reading a string from a Macro.
To use either example, simply replace the text that the variable named LookInHere is equal to. There’s a comment in the code to give you a hint.
Read Every Character in a String
This example reads every character in a string from left to right and returns the result in a message box. It makes use of the Mid function.
Sub LoopThroughString()
Dim LookInHere As String
Dim Counter As Integer
'Use your own text here
LookInHere = "AutomateExcel.com"
For Counter = 1 To Len(LookInHere)
MsgBox Mid(LookInHere, Counter, 1)
Next
End Sub
Read Every Word in a String
This example reads every word in a string from left to right and returns the result in a message box. It makes use of the Split function.
Sub LoopThroughString2()
Dim LookInHere As String
Dim Counter As Integer
Dim SplitCatcher As Variant
'Use your own text here
LookInHere = "I Heart AutomateExcel.com"
SplitCatcher = Split(LookInHere, " ")
For Counter = 0 To UBound(SplitCatcher)
MsgBox SplitCatcher(Counter)
Next
End Sub
