| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Include/Exclude Holiday - Repost to Programming Section
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Include/Exclude Holiday - Repost to Programming Section
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) "David" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Generate Payment Due Date to exclude Weekends and Public Holiday | Excel Worksheet Functions | |||
| In WORKDAY function, how to exclude multiple/variable holiday rang | Excel Discussion (Misc queries) | |||
| Alternative formula to exclude holiday calculation | Excel Worksheet Functions | |||
| Include/Exclude Holiday from Automatic Sheet Creation | Excel Discussion (Misc queries) | |||
| Use networkdays INCLUDE weekends, Exclude holidays | Excel Worksheet Functions | |||
Linear Mode
