Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
need a loop
hi
I have some code which searches a number of sheets, each containing 9 names and 2 ranges of working dates. The cell to the right of the names contains either Workplan1 or Workplan2 which determines which range on that sheet is searched. There are about 17 sheets and I would like some code that loops through all of them but I can't get it to work. Instead I have to write a separate sub for each sheet, calling the next one from the end of the previous. eg: Sub WORKSHEETS_A1() With Worksheets("A1") Set MYDSHEET = Sheet3 For COUNTER = 1 To 9 If .Cells(COUNTER, 4).Offset(0, 1) = "Work Plan 1" Then Set MYDRANGE = Sheet3.Range("A20:H82") ElseIf .Cells(COUNTER, 4).Offset(0, 1) = "Work Plan 2" Then Set MYDRANGE = Sheet3.Range("J20:Q82") End If MYdNAME = MYDSHEET.Cells(COUNTER, 4).Value Call FINDDATE_A Next COUNTER End With Call WORKSHEETS_A2 End Sub As you can imagine, for 17 sheets it's quite a lot of code. so I was hoping for something more like this Dim WS As Integer For WS = 1 To 17 With Worksheets(WS) Set MYDSHEET = Worksheets(WS) For COUNTER = 1 To 9 If .Cells(COUNTER, 4).Offset(0, 1) = "Work Plan 1" Then Set MYDRANGE = Sheet3.Range("A20:H82") ElseIf .Cells(COUNTER, 4).Offset(0, 1) = "Work Plan 2" Then Set MYDRANGE = Sheet3.Range("J20:Q82") End If MYdNAME = MYDSHEET.Cells(COUNTER, 4).Value Call FINDDATE_A Next COUNTER End With Next WS |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop | Excel Discussion (Misc queries) | |||
Loop | Excel Discussion (Misc queries) | |||
Find loop doesn't loop | Excel Discussion (Misc queries) | |||
help with a loop | Excel Discussion (Misc queries) | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming |