IF, Else, ElseIF Loop within a certain Date

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 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，它可以为您节省大量调试时间。

Answer 2

根据您的需求尝试以下代码

 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