Storing variable values in Excel worksheet

[Henry Stockbridge]

Hello,

I have created a Userform that will automate the
building of 55 Excel workbooks. Instead of
hardcoding the variables in a VBA module,
I would like to store the values in an underlying
worksheet, (A1:C55) and loop through the values.

How would I do this?

The spreadsheet data would look like this...

A B C
1 North Area_01 IC_Regn_01
2 North Area_01 PI_Regn_01
3 South Area_02 IC_Regn_04

.... and would be called using something like

Dim x as Territory
Dim y as Area
Dim z as Region

Do Until ???

' Do something with x
' Do something with y
' Do something with z

Loop

Any help you can lend would be appreciated.

Henry

 

[Roger Govier]

Hi Henry

You would need something like

Dim i as long, x as long
Dim y as String, z as string

For i = 1 to 55
x = Range("A" & i).Value : y = Range("B" & i).Value : z = Range("C" &
i).Value

do your stuff with these
Next i

 

[mudraker]

here is 2 examples of getting the data from an existing sheet into a
macro

the 1st one loads a1 to c55 into a variable array
the 2nd one displays a message box with the cells value

Sub ArrayMacro()
Dim myArr(1 To 3, 1 To 55)
Dim iRow As Integer
Dim iCol As Integer
For iRow = 1 To 54 Step 1
For iCol = 1 To 3 Step 1
myArr(iRow, iCol) = Sheets("Sheet1").Cells(iRow, iCol)
Next iCol
Next iRow

End Sub



Sub MyCode()
Dim iRow As Integer
Dim iCol As Integer
For iRow = 1 To 54 Step 1
For iCol = 1 To 3 Step 1
'do something with using the next line of code
MsgBox Sheets("Sheet1").Cells(iRow, iCol).Value
Next iCol
Next iRow
End Sub

 

[Henry Stockbridge]

Thanks for the help. I'm all set now...