VBA Leap Year/Non-Leap Year Calculation

[johnston]

I'm making a table with all the dates in a month. The user picks month and year. I have just the code listed below that calculates leap year for February and all other months of any year. The problem is that it does not calculate February during a non-leap year. The Application-defined or object-defined error message comes up during the autofill part of my code. I bolded the code that triggers the error message. Anything helps!



If monthNum=11 Or monthNum=4 Or monthNum=9 Or monthNum= 6 Then
numDays=30

ElseIf monthNum=2 Then
If year Mod 4 =0 Then
If year Mod 100= 0 Then
If year Mod 400= Then
numDays= 28
Else
numDays =29
End If

numDays=28
Else
numDays=29
End If
End If
Else
numDays=31
End If

Range("A6")=DateSerial(year, monthNum, 1)
Range("A6").Autofill Destination:=Range("A6").Resize(numDays, 1), Type:=xlFillDefault

 

[Rick Rothstein]

Thank you, this is getting me the correct number of days without extra dates, but when its formatted to Short Date it makes the monthNum and yearNum I entered a date like 1/1/1900 even though I entered monthNum as 2 and yearNum as 2018

Ah, you wanted the actual date rather than just the day number. Okay, try these two lines of code instead...

[table="width: 500"]
[tr]
	[td]  NumDays = Day(DateSerial(YearNum, MonthNum + 1, 0))
  Range("A6").Resize(NumDays) = Evaluate("IF({1},DATE(" & YearNum & "," & MonthNum & ",ROW(1:" & NumDays & ")))")[/td]
[/tr]
[/table]

 

[johnston]

Ah, you wanted the actual date rather than just the day number. Okay, try these two lines of code instead...

[TABLE="width: 500"]
<tbody>[TR]
[TD]  NumDays = Day(DateSerial(YearNum, MonthNum + 1, 0))
  Range("A6").Resize(NumDays) = Evaluate("IF({1},DATE(" & YearNum & "," & MonthNum & ",ROW(1:" & NumDays & ")))")[/TD]
[/TR]
</tbody>[/TABLE]

This is really close. When I run the macro the date comes up in the General Number format, but when I apply the Short Date format after running the macro it comes out correctly. Is there a way to make it a Short Date format and applying it to a range?
Example:
43863 in Number Format
2/2/2020 in Short Date

 

[Rick Rothstein]

This is really close. When I run the macro the date comes up in the General Number format, but when I apply the Short Date format after running the macro it comes out correctly. Is there a way to make it a Short Date format and applying it to a range?
Example:
43863 in Number Format
2/2/2020 in Short Date

Maybe add this line of code after the ones I posted...

Range("A6").Resize(NumDays).NumberFormat = "m/d/yyyy"

 

[johnston]

Maybe add this line of code after the ones I posted...

Range("A6").Resize(NumDays).NumberFormat = "m/d/yyyy"

Thank you so much. It works perfectly!