complex looping problem
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
|