Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For each syntax using a worksheet array variable
I have created abn array variable with type Worksheet to execute the same
code on a set of worksheet. I would like to use the the For Each loop to execute the code but I'm unsure of the syntax. the variable declaration: Dim wSheet(7) As Worksheet Set wSheet(1) = ActiveWorkbook.Sheets("PROG") Set wSheet(2) = ActiveWorkbook.Sheets("AUX") Set wSheet(3) = ActiveWorkbook.Sheets("SMU") Set wSheet(4) = ActiveWorkbook.Sheets("OPS") Set wSheet(5) = ActiveWorkbook.Sheets("STAFF") Set wSheet(6) = ActiveWorkbook.Sheets("BUS") Set wSheet(7) = ActiveWorkbook.Sheets("ADMIN") ..... so at this point I'm not sure how to write the loop I was thinking something like: For Each wSheet in wSheet ' a bunch of code to do the same thing with each sheet Next wSheet .... but intuituvely it doesn't seem right. For info - there are 9 sheets in the work book; the 7 above have the same structure the other 2 do not. TIA. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For each syntax using a worksheet array variable
Ralph,
Try somthing like this: Sub test() Dim ws_array Dim ws As Worksheet Dim i As Long ws_array = Array(Worksheets("Sheet1"), Worksheets("Sheet2")) For i = LBound(ws_array) To UBound(ws_array) ws_array(i).Range("A1") = "wooza" Next i End Sub hth, Doug "Ralph Heidecke" wrote in message ... I have created abn array variable with type Worksheet to execute the same code on a set of worksheet. I would like to use the the For Each loop to execute the code but I'm unsure of the syntax. the variable declaration: Dim wSheet(7) As Worksheet Set wSheet(1) = ActiveWorkbook.Sheets("PROG") Set wSheet(2) = ActiveWorkbook.Sheets("AUX") Set wSheet(3) = ActiveWorkbook.Sheets("SMU") Set wSheet(4) = ActiveWorkbook.Sheets("OPS") Set wSheet(5) = ActiveWorkbook.Sheets("STAFF") Set wSheet(6) = ActiveWorkbook.Sheets("BUS") Set wSheet(7) = ActiveWorkbook.Sheets("ADMIN") .... so at this point I'm not sure how to write the loop I was thinking something like: For Each wSheet in wSheet ' a bunch of code to do the same thing with each sheet Next wSheet ... but intuituvely it doesn't seem right. For info - there are 9 sheets in the work book; the 7 above have the same structure the other 2 do not. TIA. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Syntax for using a variable in a worksheet function | Excel Discussion (Misc queries) | |||
Syntax for variable search | Excel Programming | |||
Syntax for variable search | Excel Programming | |||
Syntax for variable search | Excel Programming | |||
Syntax for variable search | Excel Programming |