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


--
Regards

Roger Govier


"Henry Stockbridge" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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...