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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
need a loop
Denise,
Haven't tried it, but just re-cutting thye code Dim WS As Integer For WS = 1 To 17) With Worksheets(WS) For COUNTER = 1 To 9 If Cells(COUNTER, 4).Offset(0, 1) = "Work Plan 1" Then Set MYDRANGE = .Range("A20:H82") ElseIf .Cells(COUNTER, 4).Offset(0, 1) = "Work Plan 2" Then Set MYDRANGE = .Range("J20:Q82") End If MYdNAME = MYDSHEET.Cells(COUNTER, 4).Value Call FINDDATE_A Next COUNTER End With Next WS. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "DENISE" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
need a loop
you could try this. It works irrespective of the number of
sheets myNum = Application.Worksheets.count Do . . . .your code here . . . . myNum = myNum - 1 Loop Until myNum = 0 -----Original Message----- 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 . |
Reply |
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 |