VBA Code to calculate changing cell Value

[Denny57]

I am looking for a line of code that will calculate a new balance each time a new transaction entry is added.

Column H contains the account balance detail and can be either positive or negative

I have a couple of code options prepared in the example but I am struggling to get my head around how the updated balance should be calculated.

There are 4 scenarios
1) Debit Balance + new debit value
2) Debit Balance - new credit value
3) Credit Balance - new debit value
4) Credit Balance + new credit value

Negative balances will be preceded with a minus sign (-)

Any suggestions will be gratefully received.

Private Sub txtDebitAmount_Exit(ByVal Cancel As MSForms.ReturnBoolean)
txtDebitAmount.Value = Format(txtDebitAmount.Value, "Currency")
End Sub
Private Sub txtCreditAmount_Exit(ByVal Cancel As MSForms.ReturnBoolean)
txtCreditAmount.Value = Format(txtCreditAmount.Value, "Currency")
End Sub

Private Sub cmdAddUpdate_Click()

    Dim answer      As VbMsgBoxResult
    Dim AddRecord   As Boolean
    Dim sCredit As String, sDebit As String
    
    sDebit = Me.txtDebitAmount
    sCredit = Me.txtCreditAmount
    
    AddRecord = Val(Me.cmdAddUpdate.Tag) = xlAdd
    
    answer = MsgBox(IIf(AddRecord, "Add New", "Update Current") & "Record?", 36, "Information")
    
    If answer = vbYes Then
    
    If AddRecord Then CurrentRow = wsSpendingAccount.Range("A" & wsSpendingAccount.Rows.Count).End(xlUp).Row + 1
    
    On Error GoTo Myerror
    
    With wsSpendingAccount
        .Cells(CurrentRow, 1).Value = Cells(CurrentRow - 1, 1).Value + 1
        .Cells(CurrentRow, 2).Value = DTPicker1.Value
        .Cells(CurrentRow, 3).Value = txtVendor.Value
        .Cells(CurrentRow, 4).Value = cboPaymentMethod.Value
        '.Cells(CurrentRow, 5).Value = txtDebitAmount.Value
        '.Cells(CurrentRow, 6).Value = txtCreditAmount.Value
        .Cells(CurrentRow, 7).Value = cboTransactionStatus
        
    ' credit or debit
        If Len(sDebit) > 0 Then
            If Len(sCredit) > 0 Then
                MsgBox "Warning - Both Credit and Debit", vbExclamation
            Else
                .Cells(CurrentRow, 5).Value = CDbl(sDebit)
            End If
        ElseIf Len(sCredit) > 0 Then
           .Cells(CurrentRow, 6).Value = CDbl(sCredit)
        End If
        
        'If CurrentRow > 21 Then
            'Application.Goto Reference:=.Cells(r - 20, "A"), Scroll:=True
        'End If
    End With
        
End Sub

Dropbox

www.dropbox.com

 

[bobsan42]

I am not sure I completely understand your concerns, but a simple rule would be to always use addition. So:

old_debit_balance + entry_debit = new_debit_balance 
old_credit_balance + entry_credit = new_credit_balance

The only thing that you have to choose/decide is whether the credit values are negative or positive.
I personally would represent credit values with negative numbers, and then:

total_balance =  debit_balance + credit_balance

If credit values are also positive, then:

total_balance =  debit_balance - credit_balance

I hope this helps.