Adding strings to object names through a loop with if statements based on these strings

[ParetoZen]

When working with the software Stata I was able to perform the following code:

local var="1992 1991 1990 1989 1988 1987 1986 1985 1984"
foreach x of local var{
egen HHI_Y1_`x' = sum(MS_Y1^2) if Time == `x'
replace HHI_Y1 = HHI_Y1_`x' if Time == `x'
}

This worked great for generating variables (egen), alternating them and even use the local variables for conditional statements.

Now I'd like to do the same in VBA.
I got a codeblock that I want to execute with multiple ComboBoxes.
is there a way to say:

For x = 1 to 10
If Sheets("Product List").Cells(i, 1) = ComboBox`x'.Value And x > 5 Then
.Cells(ProductWorkingRow, 1) = ComboBox`x'.Value
Dim Variable_`x' as Double
'rest of codeblock
End If
Next x

Hope I made it clear what my goal is. Right now I have a dozen of these code blocks after eachother which doesnt look very efficient or professional + time consuming to replace all the x's with right number.

Thanks for the help, looking forward to participate on MrExcel.com forum.

 

[Firefly2012]

For your variable Variable_x you need to declare an array instead of the form:

Dim myVariable(1 to 10) As Double

You can refer to the comboboxes by their defined names eg:

For x = 1 to 10
  If Sheets("Product List").Cells(i, 1) = Me.Controls("ComboBox" & x).Value And x > 5 Then
   .Cells(ProductWorkingRow, 1) = Me.Controls("ComboBox" & x).Value 
    'etc etc...

 

[ParetoZen]

That works like a charm. It greatly enhances the programming speed and neatness. You opened a world for me in Excel VBA. Thanks a lot.