VBA Code to auto date cells

[data808]

I have a merged C2 and D2 cell where I will type in the month "January" for example and another cell G2 that I will type in the year 2022. I will do this for every month. Is there a way based on which month and year I type into these cells, to auto populate 1/1/22 - 1/31/22 from cells A4 - A34? I realize that not every month has 31 days and so if these happens for February for example and goes to 2/31/22, that is ok. I am just trying to create a template and we will only be using the dates that we need to and ignore the dates and don't exist. Thanks.

 

[My Aswer Is This]

Thanks and no problem on getting back to me later. It's pretty late. Thanks for replying.

Yes we will always start in A4 as this will be a template. The best solution would be to use some kind of internal calendar so it knows to auto populate A4-A34 with the correct amount of days in that month. February only has 28 days most of the time so it would only populate cells A4-A30 for example. Not sure if that's possible but if it is, that would be ideal.

Yes I know how to do that. Except for leap year where February has 28 or 29 days
I will just tell it 28 days for February which is most all years. I know how to do all the other months

 

[MARK858]

Yes I know how to do that. Except for leap year where February has 28 or 29 days

Something like the code below should take the leap year into account (it probably will work with merged cells but @data808 can test for that). Hopefully it works in all regions

Sub test1()
Dim x As Long, i As Long
Application.ScreenUpdating = False

    x = Day(WorksheetFunction.EoMonth(DateValue("03/" & Month(DateValue("03/" & Range("C2").Value & "/" & Range("G2").Value)) & "/" & Range("G2").Value), 0))
    For i = 1 To x
        Cells(3 + i, 1).Value = DateValue(i & "/" & Range("C2").Value & "/" & Range("G2").Value)
    Next
   
Application.ScreenUpdating = True
End Sub

 

[kevin9999]

Another variation, designed to run automatically if you change either the month in C2 or the year in G2. Put the code in the sheet code area of the sheet of interest.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("C2,G2"), Target) Is Nothing Then
        Application.EnableEvents = False
        Range("A4:A34").ClearContents
       
        Dim dFill As Long
        dFill = Day(WorksheetFunction.EoMonth((CDate("1/" & Evaluate("Month(1&C2)") & "/" & [G2])), 0))
        Range("A4").Value = Evaluate("Date(" & [G2] & "," & Evaluate("Month(1&C2)") & ",1)")
        Range("A4").AutoFill Range("A4").Resize(dFill), xlFillDays
       
        Application.EnableEvents = True
    End If
End Sub

 

[My Aswer Is This]

I'm back and it looks like you're getting a lot of help. So, I will move on to see if someone else here on the forum has received no help.

 

[data808]

Another variation, designed to run automatically if you change either the month in C2 or the year in G2. Put the code in the sheet code area of the sheet of interest.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("C2,G2"), Target) Is Nothing Then
        Application.EnableEvents = False
        Range("A4:A34").ClearContents
      
        Dim dFill As Long
        dFill = Day(WorksheetFunction.EoMonth((CDate("1/" & Evaluate("Month(1&C2)") & "/" & [G2])), 0))
        Range("A4").Value = Evaluate("Date(" & [G2] & "," & Evaluate("Month(1&C2)") & ",1)")
        Range("A4").AutoFill Range("A4").Resize(dFill), xlFillDays
      
        Application.EnableEvents = True
    End If
End Sub

Thanks @kevin9999. I pasted your code in the change event and its working great. Thank you so much. However, is there a way to make it stop for short months like February? Right now its going up to the 28th and then going a couple days into March. Would it be possible to have it leave those extra March days blank since its not with February? Again, thank you so much. This is super helpful!

 

[data808]

Something like the code below should take the leap year into account (it probably will work with merged cells but @data808 can test for that). Hopefully it works in all regions

Sub test1()
Dim x As Long, i As Long
Application.ScreenUpdating = False

    x = Day(WorksheetFunction.EoMonth(DateValue("03/" & Month(DateValue("03/" & Range("C2").Value & "/" & Range("G2").Value)) & "/" & Range("G2").Value), 0))
    For i = 1 To x
        Cells(3 + i, 1).Value = DateValue(i & "/" & Range("C2").Value & "/" & Range("G2").Value)
    Next
  
Application.ScreenUpdating = True
End Sub

Thanks for helping @MARK858 looks like Kevin9999 might be extremely close to what I'm looking for. Very exciting but appreciate all the help you gave me. Thank you.

 

[data808]

I'm back and it looks like you're getting a lot of help. So, I will move on to see if someone else here on the forum has received no help.

Thanks @My Aswer Is This. Yes seems like Kevin9999 is almost there in regards to what I'm looking for. Thank you very much for all your help too. Appreciate it.

 

[data808]

Another variation, designed to run automatically if you change either the month in C2 or the year in G2. Put the code in the sheet code area of the sheet of interest.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("C2,G2"), Target) Is Nothing Then
        Application.EnableEvents = False
        Range("A4:A34").ClearContents
     
        Dim dFill As Long
        dFill = Day(WorksheetFunction.EoMonth((CDate("1/" & Evaluate("Month(1&C2)") & "/" & [G2])), 0))
        Range("A4").Value = Evaluate("Date(" & [G2] & "," & Evaluate("Month(1&C2)") & ",1)")
        Range("A4").AutoFill Range("A4").Resize(dFill), xlFillDays
     
        Application.EnableEvents = True
    End If
End Sub

I been testing this code and I notice that if I trigger it by filling in C2 Month and G2 Year data then it runs great but when I delete the G2 Year data it will clear contents for A5:A34 but the A4 date still remains and it defaults to the year 2000 since it has no year data to go off of. Is there a way to have it clear A4 when I remove data from G2 or C2? I guess both C2 and G2 cells could be a requirement to reflect data in A4:A34 if not it will just clear contents if C2 and G2 required data entered is not met.

 

[kevin9999]

I been testing this code and I notice that if I trigger it by filling in C2 Month and G2 Year data then it runs great but when I delete the G2 Year data it will clear contents for A5:A34 but the A4 date still remains and it defaults to the year 2000 since it has no year data to go off of. Is there a way to have it clear A4 when I remove data from G2 or C2? I guess both C2 and G2 cells could be a requirement to reflect data in A4:A34 if not it will just clear contents if C2 and G2 required data entered is not met.

Let's try this variation first & see if it works for you.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("C2,G2"), Target) Is Nothing Then
        If [G2] = "" Then Exit Sub
        Application.EnableEvents = False
        Range("A4:A34").ClearContents
        
        Dim dFill As Long
        dFill = Day(WorksheetFunction.EoMonth((CDate("1/" & Evaluate("Month(1&C2)") & "/" & [G2])), 0))
        Range("A4").Value = Evaluate("Date(" & [G2] & "," & Evaluate("Month(1&C2)") & ",1)")
        Range("A4").AutoFill Range("A4").Resize(dFill), xlFillDays
        
        Application.EnableEvents = True
    End If
End Sub

 

[data808]

Let's try this variation first & see if it works for you.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("C2,G2"), Target) Is Nothing Then
        If [G2] = "" Then Exit Sub
        Application.EnableEvents = False
        Range("A4:A34").ClearContents
      
        Dim dFill As Long
        dFill = Day(WorksheetFunction.EoMonth((CDate("1/" & Evaluate("Month(1&C2)") & "/" & [G2])), 0))
        Range("A4").Value = Evaluate("Date(" & [G2] & "," & Evaluate("Month(1&C2)") & ",1)")
        Range("A4").AutoFill Range("A4").Resize(dFill), xlFillDays
      
        Application.EnableEvents = True
    End If
End Sub

This one isn't doing anything at all. Maybe this line is messing it up:

If [G2] = "" Then Exit Sub

Also noticed that if I take out the C2 Month data, the dates in A4:A34 are unchanged. It's only when I take out G2 Year data that the A4:A34 cells get affected which is basically everything clearing contents except for the A4 cell. The date will remain but it will change the year to a default 00 or in other words 2000.