Message box depending on the sum

[kbp]

Hi

This is a bit complex for me. I am trying to do a VBA on an excel data sheet. On this data sheet, in column E there are amounts and in column F there is either "C" (for Credit) or "D" (for debit) against the amount. So if you see the below example I have copies, 50000 D is for debit.

What I want to do is, add all the amounts against the C and all the amounts against the D, then pop up a message box to say, if debit = credit matches or does not match. The tricky part is everyday we have different amounts with different number of rows. So someday it can be 100 rows whereas someday it can be 1500 rows.

Is someone able to help me please. Thank you.


Col E Col F

[TABLE="width: 115"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD]50000
[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]4500[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]94500[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]20000[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]40000[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]75000[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]80000[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]127500[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]342500[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]50000[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]50000[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]8010[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]2685[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]80000[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]40000[/TD]
[TD]C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

Maybe this.
Will do from e1 down to as far as there is data

Sub MM1()
Dim lr As Long, c As Double, d As Double
lr = Cells(Rows.Count, "E").End(xlUp).Row
c = Application.WorksheetFunction.SumIf(Range("F1:F" & lr), "C", Range("E1:E" & lr))
d = Application.WorksheetFunction.SumIf(Range("F1:F" & lr), "D", Range("E1:E" & lr))
If c <> d Then
MsgBox "Credit of " & c & " does not equal Debit of " & d
ElseIf c = d Then
MsgBox "Credit " & c & " equals Debit " & d
End If
End Sub

 

[kbp]

Awesome, thanks very much Michael.

 

[Michael M]

Pleasure...