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

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

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

.