complex looping problem
Hi Bob and thanks very much for all your help,
The routine stops at:
With Worksheets(sh)
with an error message 13 type mismatch.
I can't see what's wrong, but then I'm in a little over my head here.
--
Regards,
Max
"Bob Phillips" wrote in message
...
Hi Max,
Sorry, missed that one
Sub test()
Worksheets(1).Activate
Dim sh As Worksheet
For Each sh In Worksheets(Array("4", "5", "6", "7", "8"))
Weeks 4, sh
Weeks 5, sh
Weeks 6, sh
Weeks 7, sh
Weeks 8, sh
Next sh
End Sub
Sub Weeks(x As Long, sh As Worksheet)
Dim r As Long 'this points to the worksheet row
Dim c As Long 'this points to the array column, not the worksheet
column
Dim SheetValues As Variant
With sh
SheetValues = Range(Cells(x, 2), Cells(x, 8)).Resize(1, 7).Value
WeekNum 7, 1, 13, SheetValues, sh
SheetValues = Range(Cells(x, 9), Cells(x, 15)).Resize(1, 7).Value
WeekNum 17, 1, 23, SheetValues, sh
End With
End Sub
Sub WeekNum(r As Long, c As Long, maxR As Long, SheetValues, sh As
Worksheet)
With Worksheets(sh)
Do While r <= maxR
Set rng = .Cells(r, 3).Resize(1, 5)
Select Case SheetValues(1, c)
Case 0
rng.Value = Array(0, 0, 0, "RDO", 0)
Case "a", "e"
rng.Value = Array("9:00", "17:21", "0:45", 0, 0)
End Select
r = r + 1
c = c + 1
Loop
End With
End Sub
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Max Bialystock" wrote in message
...
In
---
Sub WeekNum(r As Long, c As Long, maxR As Long, SheetValues)
With Worksheets(4)
---
what do I put in place of the 4 in worksheets(4)?
Sub test()
Worksheets(1).Activate
Dim sh As Worksheet
For Each sh In Worksheets(Array("4", "5", "6", "7", "8"))
Weeks 4, sh
Weeks 5, sh
Weeks 6, sh
Weeks 7, sh
Weeks 8, sh
Next sh
End Sub
Sub Weeks(x As Long, sh As Worksheet)
Dim r As Long 'this points to the worksheet row
Dim c As Long 'this points to the array column, not the worksheet
column
Dim SheetValues As Variant
With sh
SheetValues = Range(Cells(x, 2), Cells(x, 8)).Resize(1, 7).Value
WeekNum 7, 1, 13, SheetValues
SheetValues = Range(Cells(x, 9), Cells(x, 15)).Resize(1,
7).Value
WeekNum 17, 1, 23, SheetValues
End With
End Sub
Sub WeekNum(r As Long, c As Long, maxR As Long, SheetValues)
With Worksheets(4)
Do While r <= maxR
Set rng = .Cells(r, 3).Resize(1, 5)
Select Case SheetValues(1, c)
Case 0
rng.Value = Array(0, 0, 0, "RDO", 0)
Case "a", "e"
rng.Value = Array("9:00", "17:21", "0:45", 0, 0)
End Select
r = r + 1
c = c + 1
Loop
End With
End Sub
|