CODEWITHSUNDEEP
vbaexcel
karhu
karhu

Reputation: 119

How to increment through a list of words?

I have a 'For loop' in VBA with two variable elements. One is a number and one is a word. Imagine that the final output looks like this:

Result[1].Apple
Result[1].Orange
Result[1].Banana

Result[2].Apple
Result[2].Orange
Result[2].Banana

For the 1 and 2, at the beginning of my function I use: i = 1 to 2. At the end of my function I use: Next i. 

Sub Button1_Click()
For i = 0 To 5
'want to cycle through .apple, .orange, .banana after going through i values. presumably calling this variable Fruit

MyConcatenatedString = "Result[" & i & "]." & Fruit


If TypeName(Data) = "Error" Then
If MsgBox("Error reading "Result[" & i & "]." & Fruit & _
"Continue with read?", vbYesNo + vbExclamation, _
"Error") = vbNo Then Exit For
Else
'no error, put data in excel cell
End If

'send data to cell and increment i
Cells(4, 2 + i) = MyConcatenatedString

Next i

End Sub

How can I make my function cycle through Apple, Orange, and Banana like I cycle through 1 and 2 using i?

Upvotes: 1

Views: 187

Answers (1)

R3uK
R3uK

Reputation: 14537

You can use an array to define the second variable element :

Dim A() : Redim A(2)) 
A(0)="Apple" : A(1)="Orange" : ....

and to loop use For k = LBound(A) to UBound(A)

So that should look like this at the end :

Sub test_Karhu()

Dim TpStr As String
Dim A(): ReDim A(2)
A(0) = "Apple": A(1) = "Orange": A(2) = "Banana"

For i = 1 To 2
    For k = LBound(A) To UBound(A)
        TpStr = TpStr & "Result[" & i & "]." & A(k) & vbCrLf
    Next k
Next i
MsgBox TpStr

End Sub

Upvotes: 1

Related Questions