Include/Exclude Holiday - Repost to Programming Section

[David]

First, let me thank Tom Oglivy for all his assistance so far in this project!
My presention was flawless on Thursday!! Now to my LAST issue. I have a
workbook that creates days of the month for budget/sales numbers. The user
can include a % of their normal weekday sales for Saturdays, Sundays and
Holidays and a worksheet will appear for those days. If they say no to a
budget, I then have a second question that allows Saturdays, Sundays or
Holidays to appear to track sales, although there is no budget for that day.
Everything is working perfectly except the holiday when there is no budget,
but I want the sheet to appear to track sales. The sheet is not being
created. I have set up a range of holiday dates, but I think the issue is the
code I'm using is looking only for vbDays and is not recognizing my variable
for holidays. I would be so grateful if someone could help me figure out this
last bit of code so I can complete this project. Here is the code I'm using:

retry:
myDate = InputBox(prompt:="Enter the FIRST DAY of the Month you want to
Create", _
Default:=Format(Date, "mm-dd-yy"))

Worksheets("Setup").Activate
Range("X7") = myDate 'Starts first day of the month

DayDate = Day(myDate)
MonthDate = Month(myDate)
YearDate = Year(myDate)

If DayDate <> 1 Then
Msg = "You Did NOT Enter The FIRST DAY of The Month" & Chr(10) & _
" Are You SURE You Want To Continue?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbNo Then
MsgBox "OK...Try Again"
If Ans = vbNo Then GoTo retry
End If
End If

If Range("SatT") = "N" Then 'THIS WORKS!
'Msg = "Do You Want to Include Saturdays to Track Non-Budgeted Sales?"
'Saturdays will now show up
'Ans = MsgBox(Msg, vbYesNo)
'If Ans = vbNo Then
CaseSat = vbSaturday
Else
CaseSat = ""
End If
'Else
'End If

If Range("SunT") = "N" Then 'THIS WORKS!
'Msg = "Do You Want to Include Sundays to Track Non-Budgeted Sales?" 'Sunday
will now show up
'Ans = MsgBox(Msg, vbYesNo)
'If Ans = vbNo Then
CaseSun = vbSunday
Else
CaseSun = ""
End If
'Else
'End If

If Range("HolT") = "N" Then '<<<<<THIS IS NOT WORKING>>>>>
'Msg = "Do You Want to Include Holidays to Track Non-Budgeted Sales?"
'Holidays will now show up '<<<EVEN IF THEY SAY YES - I GET NOTHING>>>
'Ans = MsgBox(Msg, vbYesNo)
'If Ans = vbNo Then
CaseHol = HDay
Else
CaseHol = ""
End If
'Else
'End If

myDate = CDate(myDate)

'<<<THERE ARE TWO INSTANCES OF THE BELOW CODE, THE FIRST CREATES A COPY FOR
THE FIRST DAY OF THE MONTH, THE SECOND CREATES COPIES FOR THE REST OF THE
DAYS IN THE MONTH FROM A SECOND SHEET>>>

For iCtr = DateSerial(Year(myDate), Month(myDate), Day(myDate)) To
DateSerial(Year(myDate), Month(myDate), Day(myDate))
'For iCtr = DateSerial(Year(myDate), Month(myDate), Day(myDate)) To
DateSerial(Year(myDate), _
Month(myDate) + 1, 0) 'THIS IS THE ONE THAT WORKS BEFORE CHANGES
'For iCtr = DateSerial(Year(myDate), Month(myDate), 1) To
DateSerial(Year(myDate), _
Month(myDate) + 1, 0) 'The 1 above starts with day one.
res = Application.Match(CLng(iCtr), Range("Holidays"), 0) '<< I THINK THE
PROBLEM IS HERE>>
If IsError(res) Then
Select Case Weekday(iCtr)
Case Is = CaseSat '(Does all days, remove '& does only weekdays)
'do nothing
Case Is = CaseSun '(Does all days, remove ' & does only weekdays)
'do nothing
Case Is = CaseHol '<<THE HOLIDAY SHEET IS NOT CREATED HERE>>
'do nothing
Case Else
Application.StatusBar = D
sh.Copy after:=Sheets(Sheets.Count)
N = Sheets.Count - 5
ActiveSheet.Name = Format(iCtr, "ddd mm-dd") 'Put HOL if a Holiday Date
here
'Need to add year to check against holidays
Range("I4") = Format(iCtr, "mm-dd-yy")
Range("I10") = N
ActiveSheet.Shapes("Button 1").Select
Selection.OnAction = "EMailActiveSheet"
Range("I3").Select
End Select
End If
Next

For iCtr = DateSerial(Year(myDate), Month(myDate), Day(myDate) + 1) To
DateSerial(Year(myDate), _
Month(myDate) + 1, 0)
'For iCtr = DateSerial(Year(myDate), Month(myDate), 1) To
DateSerial(Year(myDate), _
Month(myDate) + 1, 0) 'The 1 above starts with day one.
res = Application.Match(CLng(iCtr), Range("Holidays"), 0)
If IsError(res) Then
Select Case Weekday(iCtr)
Case Is = CaseSat '(Does all days, remove '& does only weekdays)
'do nothing
Case Is = CaseSun '(Does all days, remove ' & does only weekdays)
'do nothing
Case Is = CaseHol
'do nothing
Case Else
Application.StatusBar = D
sh2.Copy after:=Sheets(Sheets.Count)
N = Sheets.Count - 6
ActiveSheet.Name = Format(iCtr, "ddd mm-dd")
Range("I4") = Format(iCtr, "mm-dd-yy")
Range("I10") = N + 1
ActiveSheet.Shapes("Button 1").Select
Selection.OnAction = "EMailActiveSheet"
Range("I3").Select
End Select
End If
Next

Sheets(6).Activate
Range("C10") = N + 1

Call ListSheets

For wCtr = 6 To Worksheets.Count
'If Worksheets(wCtr).Name = Worksheets("Setup").Name Then
'skip it
'Else
Worksheets(wCtr).Activate
Range("C10") = N + 1
Call ChangeSheetName
'End If
Next wCtr

'For wCtr = 5 To Worksheets.Count
'Worksheets(wCtr).Activate
'DDate = myDate + 1
'Range("H4") = DDate
'myDate = DDate + 1
'Next wCtr

Sheets(6).Activate
Range("I3").Select

 

[David]

I figured it OUT! YIPPEE!!
For those that might be interested, I created a false range (old) and then
used the variable HolRange to set what the match was looking for. Here is how
I did it....

If Range("HolT") = "N" Then
HolRange = "Holidays"
Else
HolRange = "Old"
End If

myDate = CDate(myDate)

For iCtr = DateSerial(Year(myDate), Month(myDate), Day(myDate)) To
DateSerial(Year(myDate), Month(myDate), Day(myDate))

res = Application.Match(CLng(iCtr), Range(HolRange), 0)