Message box depending on the sum

kbp

New Member
Joined
May 14, 2012
Messages
36
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

50000
D
4500D
94500C
20000D
40000D
75000D
80000D
127500D
342500C
50000D
50000C
8010C
2685C
80000C
40000C

<tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,187
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Maybe this.
Will do from e1 down to as far as there is data

Code:
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
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,187
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Pleasure...(y)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,187
Messages
5,640,693
Members
417,161
Latest member
Devon150

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top