VBA Date Issue!!!

M

mayaicar

New Member
Joined
Sep 1, 2021
Messages
8
Office Version
  1. 2019
Platform
  1. Windows
I have created a macro for each month and would like it to run on workday 4 of the following month until the next month workday 4. Below I have created the code to call each monthly macro given a date range, however when I run the date_range sub it doesn't seem to account for the date range. I have included the July macro it should call if the month is August workday 4 for visibility (the macro is very similar for each month so only included this one macro).

The code can be found below. If you could please give any aid as the date range and format is correct to my best knowledge.

VBA Code: 
Sub Date_Range()

Dim StartDate As Date
Dim EndDate As Date
Dim result As String

StartDate = Now()
'dateformat is m/dd/yyyy

If StartDate >= #8/5/2021# And EndDate < #9/5/2021# Then
    Call July_drop_down

ElseIf StartDate >= #9/6/2021# And EndDate < #10/5/2021# Then
    Call August_drop_down

ElseIf StartDate >= #10/6/2021# And EndDate < #11/3/2021# Then
    Call September_drop_down

ElseIf StartDate >= #11/4/2021# And EndDate < #12/5/2021# Then
    Call October_drop_down

ElseIf StartDate >= #12/6/2021# And EndDate < #1/5/2022# Then
    Call November_drop_down

ElseIf StartDate >= #1/6/2022# And EndDate < #2/3/2022# Then
    Call December_drop_down

ElseIf StartDate >= #2/4/2022# And EndDate < #3/3/2022# Then
    Call January_drop_down

ElseIf StartDate >= #3/4/2022# And EndDate < #4/5/2022# Then
    Call February_drop_down

ElseIf StartDate >= #4/6/2022# And EndDate < #5/4/2022# Then
    Call March_drop_down

ElseIf StartDate >= #5/5/2022# And EndDate < #6/5/2022# Then
    Call April_drop_down

ElseIf StartDate >= #6/6/2022# And EndDate < #7/5/2022# Then
    Call May_drop_down

ElseIf StartDate >= #7/6/2022# And EndDate < #8/3/2022# Then
    Call June_drop_down
Else
    MsgBox ("out of date")
End If

End Sub

Sub July_drop_down()
'
' July_drop_down Macro
' All tabs within the file will choose July actuals. Designed to run on WD4 August.  Month = 7 Quarter = 3
'
' Keyboard Shortcut: Ctrl+Shift+l
'

Range("K51").Select
    Sheets("Abs. $MM - Spot FX").Select
    Range("K9").Value = "ALL"
    Range("K10").Value = "ALL"
    Range("K11").Value = 7
    Range("K12").Value = 2021
    Range("K13").Value = "Actuals"
    Range("K15").Value = "ALL"
    Range("K16").Value = 3
    Range("K17").Value = "ALL"
    Range("K18").Value = 2021
    Range("K19").Value = "Actuals"
    Range("K21").Value = "ALL"
    Range("K22").Value = "ALL"
    Range("K23").Value = 7
    Range("K24").Value = 2021
    Range("K25").Value = "Guidance Q3"
    Range("K27").Value = "Spot FX"
Range("K40").Select
    Sheets("Abs. $MM - Constant FX").Select
    Range("K9").Value = "ALL"
    Range("K10").Value = "ALL"
    Range("K11").Value = 7
    Range("K12").Value = 2021
    Range("K13").Value = "Actuals"
    Range("K15").Value = "ALL"
    Range("K16").Value = 3
    Range("K17").Value = "ALL"
    Range("K18").Value = 2021
    Range("K19").Value = "Actuals"
    Range("K21").Value = "ALL"
    Range("K22").Value = "ALL"
    Range("K23").Value = 7
    Range("K24").Value = 2021
    Range("K25").Value = "Guidance Q3"
    Range("K27").Value = "Constant FX"
Range("K51").Select
    Sheets("% GMS").Select
    Range("K9").Value = "ALL"
    Range("K10").Value = "ALL"
    Range("K11").Value = 7
    Range("K12").Value = 2021
    Range("K13").Value = "Actuals"
    Range("K15").Value = "ALL"
    Range("K16").Value = 3
    Range("K17").Value = "ALL"
    Range("K18").Value = 2021
    Range("K19").Value = "Actuals"
    Range("K21").Value = "ALL"
    Range("K22").Value = "ALL"
    Range("K23").Value = 7
    Range("K24").Value = 2021
    Range("K25").Value = "Guidance Q3"
    Range("K27").Value = "Constant FX"
Range("K54").Select
    Sheets("$ Per Order").Select
    Range("K9").Value = "ALL"
    Range("K10").Value = "ALL"
    Range("K11").Value = 7
    Range("K12").Value = 2021
    Range("K13").Value = "Actuals"
    Range("K15").Value = "ALL"
    Range("K16").Value = 3
    Range("K17").Value = "ALL"
    Range("K18").Value = 2021
    Range("K19").Value = "Actuals"
    Range("K21").Value = "ALL"
    Range("K22").Value = "ALL"
    Range("K23").Value = 7
    Range("K24").Value = 2021
    Range("K25").Value = "Guidance Q3"
    Range("K27").Value = "Constant FX"
Range("K60").Select
    Sheets("$ Per Unit").Select
    Range("K9").Value = "ALL"
    Range("K10").Value = "ALL"
    Range("K11").Value = 7
    Range("K12").Value = 2021
    Range("K13").Value = "Actuals"
    Range("K15").Value = "ALL"
    Range("K16").Value = 3
    Range("K17").Value = "ALL"
    Range("K18").Value = 2021
    Range("K19").Value = "Actuals"
    Range("K21").Value = "ALL"
    Range("K22").Value = "ALL"
    Range("K23").Value = 7
    Range("K24").Value = 2021
    Range("K25").Value = "Guidance Q3"
    Range("K27").Value = "Constant FX"
Range("K40").Select
    Sheets("$ Per Unit | ProcP focus").Select
    Range("K9").Value = "ALL"
    Range("K10").Value = "ALL"
    Range("K11").Value = 7
    Range("K12").Value = 2021
    Range("K13").Value = "Actuals"
    Range("K15").Value = "ALL"
    Range("K16").Value = 3
    Range("K17").Value = "ALL"
    Range("K18").Value = 2021
    Range("K19").Value = "Actuals"
    Range("K21").Value = "ALL"
    Range("K22").Value = "ALL"
    Range("K23").Value = 7
    Range("K24").Value = 2021
    Range("K25").Value = "Guidance Q3"
    Range("K27").Value = "Constant FX"
Range("M44").Select
    Sheets("Conventional OPPU view").Select
    Range("S1").Value = "Retail"
    Range("S2").Value = "INT6"
    Range("S3").Value = "2021"
    Range("S4").Value = "ALL"
    Range("S5").Value = "3"
    Range("S6").Value = "YTD"
    Range("S7").Value = "Actuals"
    Range("T1").Value = "Retail"
    Range("T2").Value = "UK"
    Range("T3").Value = "2021"
    Range("T4").Value = "ALL"
    Range("T5").Value = "3"
    Range("T6").Value = "YTD"
    Range("T7").Value = "Actuals"
    Range("U1").Value = "Retail"
    Range("U2").Value = "DE"
    Range("U3").Value = "2021"
    Range("U4").Value = "ALL"
    Range("U5").Value = "3"
    Range("U6").Value = "YTD"
    Range("U7").Value = "Actuals"
    Range("V1").Value = "Retail"
    Range("V2").Value = "FR"
    Range("V3").Value = "2021"
    Range("V4").Value = "ALL"
    Range("V5").Value = "3"
    Range("V6").Value = "YTD"
    Range("V7").Value = "Actuals"
    Range("W1").Value = "Retail"
    Range("W2").Value = "IT"
    Range("W3").Value = "2021"
    Range("W4").Value = "ALL"
    Range("W5").Value = "3"
    Range("W6").Value = "YTD"
    Range("W7").Value = "Actuals"
    Range("X1").Value = "Retail"
    Range("X2").Value = "ES"
    Range("X3").Value = "2021"
    Range("X4").Value = "ALL"
    Range("X5").Value = "3"
    Range("X6").Value = "YTD"
    Range("X7").Value = "Actuals"
    Range("Y1").Value = "Retail"
    Range("Y2").Value = "JP"
    Range("Y3").Value = "2021"
    Range("Y4").Value = "ALL"
    Range("Y5").Value = "3"
    Range("Y6").Value = "YTD"
    Range("Y7").Value = "Actuals"
    Range("AA1").Value = "Retail"
    Range("AA2").Value = "INT6"
    Range("AA3").Value = "2021"
    Range("AA4").Value = "ALL"
    Range("AA5").Value = "3"
    Range("AA6").Value = "YTD"
    Range("AA7").Value = "Actuals"
    Range("AA8").Value = "% GMS"
    Range("AB1").Value = "Retail"
    Range("AB2").Value = "UK"
    Range("AB3").Value = "2021"
    Range("AB4").Value = "ALL"
    Range("AB5").Value = "3"
    Range("AB6").Value = "YTD"
    Range("AB7").Value = "Actuals"
    Range("AB8").Value = "% GMS"
    Range("AC1").Value = "Retail"
    Range("AC2").Value = "DE"
    Range("AC3").Value = "2021"
    Range("AC4").Value = "ALL"
    Range("AC5").Value = "3"
    Range("AC6").Value = "YTD"
    Range("AC7").Value = "Actuals"
    Range("AC8").Value = "% GMS"
    Range("AD1").Value = "Retail"
    Range("AD2").Value = "FR"
    Range("AD3").Value = "2021"
    Range("AD4").Value = "ALL"
    Range("AD5").Value = "3"
    Range("AD6").Value = "YTD"
    Range("AD7").Value = "Actuals"
    Range("AD8").Value = "% GMS"
    Range("AE1").Value = "Retail"
    Range("AE2").Value = "IT"
    Range("AE3").Value = "2021"
    Range("AE4").Value = "ALL"
    Range("AE5").Value = "3"
    Range("AE6").Value = "YTD"
    Range("AE7").Value = "Actuals"
    Range("AE8").Value = "% GMS"
    Range("AF1").Value = "Retail"
    Range("AF2").Value = "ES"
    Range("AF3").Value = "2021"
    Range("AF4").Value = "ALL"
    Range("AF5").Value = "3"
    Range("AF6").Value = "YTD"
    Range("AF7").Value = "Actuals"
    Range("AF8").Value = "% GMS"
    Range("AG1").Value = "Retail"
    Range("AG2").Value = "JP"
    Range("AG3").Value = "2021"
    Range("AG4").Value = "ALL"
    Range("AG5").Value = "3"
    Range("AG6").Value = "YTD"
    Range("AG7").Value = "Actuals"
    Range("AG8").Value = "% GMS"
    Range("AI1").Value = "Retail"
    Range("AI2").Value = "INT6"
    Range("AI3").Value = "2021"
    Range("AI4").Value = "ALL"
    Range("AI5").Value = "3"
    Range("AI6").Value = "YTD"
    Range("AI7").Value = "Actuals"
    Range("AI8").Value = "$ per unit"
    Range("AJ1").Value = "Retail"
    Range("AJ2").Value = "UK"
    Range("AJ3").Value = "2021"
    Range("AJ4").Value = "ALL"
    Range("AJ5").Value = "3"
    Range("AJ6").Value = "YTD"
    Range("AJ7").Value = "Actuals"
    Range("AJ8").Value = "$ per unit"
    Range("AK1").Value = "Retail"
    Range("AK2").Value = "DE"
    Range("AK3").Value = "2021"
    Range("AK4").Value = "ALL"
    Range("AK5").Value = "3"
    Range("AK6").Value = "YTD"
    Range("AK7").Value = "Actuals"
    Range("AK8").Value = "$ per unit"
    Range("AL1").Value = "Retail"
    Range("AL2").Value = "FR"
    Range("AL3").Value = "2021"
    Range("AL4").Value = "ALL"
    Range("AL5").Value = "3"
    Range("AL6").Value = "YTD"
    Range("AL7").Value = "Actuals"
    Range("AL8").Value = "$ per unit"
    Range("AM1").Value = "Retail"
    Range("AM2").Value = "IT"
    Range("AM3").Value = "2021"
    Range("AM4").Value = "ALL"
    Range("AM5").Value = "3"
    Range("AM6").Value = "YTD"
    Range("AM7").Value = "Actuals"
    Range("AM8").Value = "$ per unit"
    Range("AN1").Value = "Retail"
    Range("AN2").Value = "ES"
    Range("AN3").Value = "2021"
    Range("AN4").Value = "ALL"
    Range("AN5").Value = "3"
    Range("AN6").Value = "YTD"
    Range("AN7").Value = "Actuals"
    Range("AN8").Value = "$ per unit"
    Range("AO1").Value = "Retail"
    Range("AO2").Value = "JP"
    Range("AO3").Value = "2021"
    Range("AO4").Value = "ALL"
    Range("AO5").Value = "3"
    Range("AO6").Value = "YTD"
    Range("AO7").Value = "Actuals"
    Range("AO8").Value = "$ per unit"
Range("AL10").Select
    Sheets("Top & Bottom Line").Select
    Range("K9").Value = "ALL"
    Range("K10").Value = 3
    Range("K11").Value = "ALL"
    Range("K12").Value = 2021
    Range("K13").Value = "Actuals"
    Range("K15").Value = "ALL"
    Range("K16").Value = 3
    Range("K17").Value = "ALL"
    Range("K18").Value = 2020
    Range("K19").Value = "Actuals"
    Range("K21").Value = "ALL"
    Range("K22").Value = 3
    Range("K23").Value = "ALL"
    Range("K24").Value = 2021
    Range("K25").Value = "Guidance Q3"
    Range("K27").Value = "Constant FX"
    'Call timer
    ActiveWorkbook.Save
End Sub
 
Last edited by a moderator:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Are the Worksheets you are writing too in existence before the macro runs?
I see you are not checking for their existence and creating them if not, nor are you clearing all cells before writing to those work sheets.

For the IF staements, you might be better served using CASE...SELECT
Instead of selecting each sheet to write to, you might consider using WITH SHEETS(name).
 
Upvote 0
@mayaicar
You could perhaps be more efficient with some of the coding but i think the main issue would be the fact that you do not set a value for EndDate. Hence it remains at value 0 and negates all your tests?
Hope that helps.
 
Upvote 0
Solution
SpillerBD said:
Are the Worksheets you are writing too in existence before the macro runs?
I see you are not checking for their existence and creating them if not, nor are you clearing all cells before writing to those work sheets.

For the IF staements, you might be better served using CASE...SELECT
Instead of selecting each sheet to write to, you might consider using WITH SHEETS(name).
Click to expand...
Yes, the worksheets are open. Thanks for the help though, appreciated :)
 
Upvote 0
Snakehips said:
@mayaicar
You could perhaps be more efficient with some of the coding but i think the main issue would be the fact that you do not set a value for EndDate. Hence it remains at value 0 and negates all your tests?
Hope that helps.
Click to expand...
Silly me! Thank you, this solved my issue.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Modify VBA code to sort by date ignoring words, please :)
2
Replies
14
Views
2K
dakotacondon1
D
W
  • Question Question
Trying to learn how to loop code
Replies
1
Views
238
mumps
mumps
Agnarr
  • Question Question
VBA problem with creating new sheets
Replies
0
Views
832
Agnarr
Agnarr
C
  • Question Question
VBA-Photos to be imported from Sharepoint Site rather than local drive
Replies
1
Views
648
Micron
M
Burrgogi
  • Solved
VBA solution for a tally sheet, add 1 to a cell based on current date
2
Replies
10
Views
936
Burrgogi
Burrgogi

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top