Loop with Changing Variable Names

[Lloyd Christmas]

I am trying to write a VBA macro without my Mr. Excel macro books available. I am going to make this simplistic as possible, hopefully it makes sense:

I have several variables (ex: Store1, Store2, Store3, Store4).

I have a process that does this (greatly simplified):

If Store1 = "Open" then ....
If Store2 = "Open" then....
If Store3 = "Open" then....
If Store4 = "Open" then...

I would like to just create a loop to clean it up (there are quite a bit more than 4, and I don't want to have hundreds of repeating lines of code)

For i = 1 to 4
If Store(i) = "Open" then.....
Next i

In this example, the first time through it would be Store1, the second time through it would be Store2, etc.

I can't quite get the syntax on the Store(i) part though. Store&i didn't work. Nor did "Store"&i

How do I refer to a variable name that is partially variable itself?

 

[Colin Legg]

Hi,

Short answer, you can't.

What you (probably) need here is an array: one variable which holds all four values. You can then loop through the array.

Sub test()
    Dim Stores(0 To 4) As String
    Dim i As Long
 
    'put some values in the array for this example
    Stores(0) = "Open"
    Stores(1) = "Closed"
    Stores(2) = "Open"
    Stores(3) = "Open"
 
 
    For i = LBound(Stores) To UBound(Stores)
        If Stores(i) = "Open" Then
            MsgBox "Element " & i & " " & Stores(i)
        End If
    Next i
 
End Sub

 

[Lloyd Christmas]

That worked awesome, and cleaned the code up even more than I had hoped.

Thanks much.

 

[Colin Legg]

You're welcome.

Of course, there was a typo in the example I gave you. This line:

Dim Stores(0 To 4) As String

Should have read

Dim Stores(0 To 3) As String