ExcelBanter - For Each Sheet in Workbook

this is my best guess at what you describe. It probably isn't correct, but
I find the first sheet that has a name starting with "EDL " and Range("O1")
UFdate

Then for SwpGn sheets (starting with that sheet), it enters 24 according to
your rules, then for the next SwpGn sheets, it enters 24 using the opposite
rules.

Sub GenSwp()
Dim DifInDys As Integer, SwpGn As Integer
Dim Ct As Integer
Dim UFdate As Date, DtRg As Date
Dim Sh As Worksheet, wsData As Worksheet
Dim bSheetFound As Boolean
Dim i As Long
Set wsData = Worksheets("Data")
UFdate = wsData.Range("E26").Value
SwpGn = wsData.Range("E24").Value
Set Sh = Worksheets(1)
bSheetFound = False

' find sheet to start on

For Each Sh In Worksheets
If Sh.Name Like "EDL *" Then
DtRg = Sh.Range("O1").Value
If DtRg = UFdate Then
bSheetFound = True
Exit For
End If
End If
Next
DifInDys = DtRg - UFdate
If Not bSheetFound Then Exit Sub
Set sh1 = Sh
For i = 1 To SwpGn
If Abs(DifInDys) Mod 2 = 1 Then
Select Case True
Case Is = wsData.Range("E20")
sh1.Range("H23").Value = ""
sh1.Range("J23") = 24
Case Is = wsData.Range("E22")
sh1.Range("J23").Value = ""
sh1.Range("H23") = 24
End Select
Else
Select Case True
Case Is = wsData.Range("E20")
sh1.Range("J23").Value = ""
sh1.Range("H23") = 24
Case Is = wsData.Range("E22")
sh1.Range("H23").Value = ""
sh1.Range("J23") = 24
End Select
End If
Set sh1 = sh1.Next
Next
For i = 1 To SwpGn
If Abs(DifInDys) Mod 2 < 1 Then
Select Case True
Case Is = wsData.Range("E20")
sh1.Range("H23").Value = ""
sh1.Range("J23") = 24
Case Is = wsData.Range("E22")
sh1.Range("J23").Value = ""
sh1.Range("H23") = 24
End Select
Else
Select Case True
Case Is = wsData.Range("E20")
sh1.Range("J23").Value = ""
sh1.Range("H23") = 24
Case Is = wsData.Range("E22")
sh1.Range("H23").Value = ""
sh1.Range("J23") = 24
End Select
End If
Set sh1 = sh1.Next
Next

End Sub

--
Regards,
Tom Ogilvy

Lloyd Peck wrote in message
...
I wasnt really trying to establish a 3D range, I was trying to get VBA
to return the value on the same range on each WS.
The nested For Each Sh In ThisWorkbook.Worksheets did just that.

Sub GenSwp()
Dim DifInDys, SwpGn, Ct As Integer
Dim UFdate, DtRg As Date
Dim Sh, wsData As Worksheet
Set wsData = Worksheets("Data")
UFdate = wsData.Range("E26").Value
SwpGn = wsData.Range("E24").Value
Select Case SwpGn
Case Is = 1
For Each Sh In ThisWorkbook.Worksheets
If Sh.Name Like "EDL *" Then
DtRg = Sh.Range("O1").Value
If DtRg = UFdate Then
DifInDys = DtRg - UFdate
If Abs(DifInDys) Mod 2 = 1 Then
Select Case True
Case Is = wsData.Range("E20")
Sh.Range("H23").Value = ""
Sh.Range("J23") = 24
Case Is = wsData.Range("E22")
Sh.Range("J23").Value = ""
Sh.Range("H23") = 24
End Select
Else
Select Case True
Case Is = wsData.Range("E20")
Sh.Range("J23").Value = ""
Sh.Range("H23") = 24
Case Is = wsData.Range("E22")
Sh.Range("H23").Value = ""
Sh.Range("J23") = 24
End Select
End If
End If
End If
Next Sh
Case Is = 2

This is the code Im working on.WS(Data) is a hidden Sh. The values
SwpGn,and UFdate are edited thru a UserForm, SwpGn can be an integer
from 1-7. The Select Case True test value is entered from the same
UserForm by two Checkboxes.This code enters the integer 24 alternatly
between two ranges depending on the date in DtRg.
It works when SwpGn.value=1. But if SwpGn.value was to equal to another
number,, say, 2, then I need the 24 to be entered for two days in a row
in one column, then two days in a row in the other column. SwpGn=3 then
24 needs to be in the same column for three days straight, then the
other column for the next 3 days.

Im a little stuck right now, What I need to do for Case Is = 2, is for
VBA to run the code then go to Next Sh (next day), and do the same thing
for that sheet. Then go to the next two Sh's and place the value 24 in
the alternate column. So on...
Then Ill be at Case Is = 3 !! all the way to Case Is = 7...
I have considered adding a For Ct= 0 To * depending on SwpGn.value, If
DtRg = UFdate+Ct Then
But say SwpGn.value=2
For Ct= 0 To 1
If DtRg = UFdate+Ct Then
This would only run through the WS that matched UFdate and the next day.
So,,, thats what Im working on.
Aint ya glad ya asked!!?
Lloyd

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!