jeudi 17 juin 2021

Microsoft Excel's VBA: If Statements running incorrectly, Always Run [duplicate]

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