如果，否则，ElseIF循环在某个日期内

Question

我想设置以下条件，但我只能得到两个条件

这三个条件是小于7天乘以一个值，范围从2个日期乘以一个值和30天以上乘以另一个值。

不能让一切都开始工作

不知道哪里出了问题

' To create the following condition
 'If less than 7 days interest = 0%
' if 8 to 30 days interest = 7%
 'if more than 31 days interest = 9%

Sub Workbook_Open()

For i = 1 To 3 'Rows.Count
xdate = Cells(i, 1)

nulldate = DateAdd("d", -7, Date)
irate7late = DateAdd("d", -8, Date)
irate7early = DateAdd("d", -30, Date)


If Day(nulldate) < Day(xdate) Then
    result = Cells(i, 2) * 1
ElseIf Day(irate7early) <= Day(xdate) And Day(xdate) <= Day(irate7late) Then
            '30/9/2015      20/10/2015      20/10/2015      22/10/2015
    result = Cells(i, 2) * 1.07

ElseIf Day(irate7early) > Day(xdate) Then
    result = Cells(i, 2) * 1.09
End If

Cells(i, 3).Value = result

Next i

End Sub

Answer 1

为您的需求尝试下面的代码

 Sub Workbook_Open()

Dim diffdate As Variant

For i = 1 To 3 'Rows.Count

      xdate = Cells(i, 1).Value
      diffdate = (DateDiff("d", xdate, Now()))

        If diffdate < 7 Then
            result = Cells(i, 2) * 1
        ElseIf diffdate < 31 And diffdate > 7 Then
            result = Cells(i, 2) * 1.07
        Else
            result = Cells(i, 2) * 1.09
        End If

    Cells(i, 3).Value = result

Next

End Sub 

Answer 2

逆转您的测试有时会简化它们：

Sub Workbook_Open()

    Dim delta as Long
    Dim xdate as Date
    For i = 1 To 3 'Rows.Count
        xdate = Cells(i, 1).Value
        delta = DateDiff("d", xdate, Date)

        If delta > 30 Then
            Cells(i,3).Value = Cells(i,2).Value * 1.09
        ElseIf delta > 7 Then
            Cells(i,3).Value = Cells(i,2).Value * 1.07
        Else 'delta <= 7
            Cells(i,3).Value = Cells(i,2).Value
        End If
    Next i

End Sub

不要忘记Option Explicit，它可能会为您节省大量调试时间。