Find condition for different Date Range

[junyi.ho]

Hi all this is my first time coding from scratch and I have hit a roadblock I am trying to create the following 3 condition but I am at best only able to get two to work.

Could any one advice on where went wrong?

'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)
'MsgBox 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

 

[Crystalyzer]

Try this

Sub Workbook_Open()
    Dim sht As Worksheet
    Dim r As Integer
    Dim i As Integer
    Dim days As Long
    
    Set sht = ActiveSheet
    
    With sht
        r = .Cells(.Rows.Count, "A").End(xlUp).Row
    
        For i = 1 To r
                        
            days = DateDiff("d", .Cells(i, 1), Date)
                        
            Select Case days
                Case Is >= 31
                    result = .Cells(i, 2) * 1.09
                Case Is >= 8
                    result = .Cells(i, 2) * 1.07
                Case Else
                    result = .Cells(i, 2)
            End Select
            
            .Cells(i, 3).Value = result
        
        Next i
    End With
End Sub