CODEWITHSUNDEEP
vbaexcelamortization
Mohammed Alamoudi
Mohammed Alamoudi

Reputation: 21

How to check cell value to change value of another?

I am working on a loan amortization project.

The loan is amortized over a period of time. Let's say 20 years which has 239 payment lines.

The last row which is generated automatically via an if formula needs some Reconciliations
enter image description here

As long as the last scheduled payment is less than the actual payment which is 6309 then the scheduled payment should equal 2711 + 4
enter image description here

I have been trying to edit this code.

Private Sub CommandButton23_Click()

    Dim r1, r2, n As Long
    Dim Pay_Num As Integer, result As String
    Pay_Num = Range("D34").Value
        
    With Sheets("LOANQUIC & Schedule Table") '~~> change to suit
        Dim lrow As Long
        Number_of_Payments = Range("G20").Value
        lrow = .Range("A" & .Rows.Count).End(xlUp).Row
        r1 = Application.Transpose(.Range("A2:A" & lrow))
        r2 = Application.Transpose(.Range("J2:J" & lrow))
        For n = LBound(r1) To UBound(r1)
            If r1(n) <> "" Then r2(n) = r1(n)
            If r1(n) = Number_of_Payments Then Sched_Pay = Number_of_Payments
            Range("D35").Value = Sched_Pay
        Next
        .Range("J2:J" & lrow) = Application.Transpose(r2)
    End With
    
End Sub

Upvotes: 3

Views: 477

Answers (1)

PeterT
PeterT

Reputation: 8557

Working under the assumption, based on your example, that the last row will always contain your last payment, you can just make a quick check on the values and fill in the final payment amount:

Option Explicit

Sub Example1()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim begBalColumn As Long
    Dim schedPayColumn As Long
    Dim interestColumn As Long
    Dim endBalColumn As Long

    '--- you can automatically set these values if needed
    begBalColumn = 3
    schedPayColumn = 4
    interestColumn = 6
    endBalColumn = 7

    '--- assumes the last row has your last payment
    Set ws = ActiveSheet
    lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

    With ws
        If .Cells(lastRow, begBalColumn).Value < _
               .Cells(lastRow - 1, schedPayColumn).Value Then
            .Cells(lastRow, schedPayColumn).Value = .Cells(lastRow, begBalColumn).Value + _
                                                    .Cells(lastRow, interestColumn).Value
        End If
    End With
End Sub

Otherwise, it's not immediately apparent why you're needing to scan all the rows in your amortization table to find the last row.

Alternatively, you can also use a formula in cell G5 in your example:

=IF(C5<D4,C5+F5,D4)

Upvotes: 0

Related Questions