I'm new to VBA and trying to write something that will fill in a column with the appropriate dates for the first of the month date entered in cell D3. Ex. If 5/1/2021 is entered in D3, the dates 5/1/2021 - 5/31/2021 will be outputted into the B column starting from row 5. For some reason, even though the month of the entered date is correctly read (for ex. 5 from 5/1/2021) I get the wrong days back. For 5/1/2021 I get 30 days. This is despite the fact that 5 is not equal to any of the numbers in the if statement for the months with 30 days. It seems whichever statement is first in line is completed. When I was using simple Ifs instead of If/Else statements, the whole thing ran despite the logical statement being False in certain cases. I don't know much about this language so I'm hoping it's a simple syntax fix. Why is this happening and how can I fix it so that the logical statements are read correctly? My code is below. Thank you so much!
VBA Code:
Sub FillDays()
Dim row As Double
row = 0
Dim startdate As Date
Dim enddate As Date
startdate = Range("D3").Value
If Month(startdate) = 4 Or 6 Or 9 Or 11 Then
enddate = DateAdd("d", 29, startdate)
' 30 days
Range("B6").Select
Do Until DateAdd("d", 1, startdate) = enddate + 1
Activecell.Offset(row, 0).Value = DateAdd("d", 1, startdate)
startdate = startdate + 1
row = row + 1
Loop
ElseIf Month(startdate) = 2 Then
enddate = DateAdd("d", 27, startdate)
' 28 days
Range("B6").Select
Do Until DateAdd("d", 1, startdate) = enddate + 1
Activecell.Offset(row, 0).Value = DateAdd("d", 1, startdate)
startdate = startdate + 1
row = row + 1
Loop
ElseIf Month(startdate) = 2 And isLeapYear(Year(startdate)) = True Then
enddate = DateAdd("d", 28, startdate)
' 29 days
Range("B6").Select
Do Until DateAdd("d", 1, startdate) = enddate + 1
Activecell.Offset(row, 0).Value = DateAdd("d", 1, startdate)
startdate = startdate + 1
row = row + 1
Loop
ElseIf Month(startdate) = 1 Or 3 Or 5 Or 7 Or 8 Or 10 Or 12 Then
enddate = DateAdd("d", 30, startdate)
' 31 days
Range("B6").Select
Do Until DateAdd("d", 1, startdate) = enddate + 1
Activecell.Offset(row, 0).Value = DateAdd("d", 1, startdate)
startdate = startdate + 1
row = row + 1
Loop
End If
End Sub
Aucun commentaire:
Enregistrer un commentaire