Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Storing variable values in Excel worksheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Storing variable values in Excel worksheet
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" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Storing variable values in Excel worksheet
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 -- mudraker ------------------------------------------------------------------------ mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473 View this thread: http://www.excelforum.com/showthread...hreadid=535903 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Storing variable values in Excel worksheet
Thanks for the help. I'm all set now...
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Storing a value to variable | Excel Discussion (Misc queries) | |||
excel storing previous cell values in memory | Excel Worksheet Functions | |||
Storing variable in Option.Tag | Excel Programming | |||
Storing a Macro name in a Variable | Excel Programming | |||
Attaching a JET database to an Excel Worksheet OR storing large binary data in a worksheet | Excel Programming |