IF, Else, ElseIF Loop within a certain Date

Question

I want to set the following condition but i can only get 2 condition to be done

The three condition are less than 7 days multiple by one value, the range between 2 dates multiply by one value and more than 30 days multiply by another value.

Not able to get all to work

Not sure what 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)

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

Vincent G · Accepted Answer · 2015-10-30 08:08:06Z

1

Reversing your test may simplify them sometimes:

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

And don't forget Option Explicit, it may save you a lot of time in debugging.

answered Oct 30, 2015 at 8:08

Vincent G

3,1981 gold badge16 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

PASUMPON V N · Accepted Answer · 2015-10-30 07:49:52Z

0

Try this below code for your requirement

 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

edited Oct 30, 2015 at 7:49

answered Oct 30, 2015 at 7:41

PASUMPON V N

1,1862 gold badges10 silver badges17 bronze badges

Collectives™ on Stack Overflow

IF, Else, ElseIF Loop within a certain Date

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related