Hi,
I need to treat my problem when show TOTAL row to sum columns I:L .
I put the right way how should be , but when running the macro will not sum and show TOTAL word in error location.
thanks
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Sum TOTAL for each column in last row
0
Discussion
Hasson
I don't have time to check today but your code seems to get the correct figures from VB- if I use the Subtotals feature in Excel, it gives these for DEBITand CREDIT:
With the BALANCE figure from CLS, they look okay.
I don't have time to check today but your code seems to get the correct figures from VB- if I use the Subtotals feature in Excel, it gives these for DEBITand CREDIT:
AHHGGJ-1 Total 3,200.00 0.00
AHHGGJ-2 Total 3,000.00 1,300.00
AHHGGJ-3 Total 2,000.00 0.00
AHHGGJ-4 Total 1,500.00 0.00
AHHGGJ-5 Total 1,000.00 0.00With the BALANCE figure from CLS, they look okay.
John_Ru (rep: 6417) Sep 29, '24 at 2:01 am
Hi,
what I meant the TOTAL row should sum the whole columns I:L
should sum the wholes amounts for columns DEBIT,CREDIT,TOTAL
so in lastrow will be
TOTAL 77,538.00 10,700.00 1,300.00 86,938.00
what I meant the TOTAL row should sum the whole columns I:L
should sum the wholes amounts for columns DEBIT,CREDIT,TOTAL
so in lastrow will be
TOTAL 77,538.00 10,700.00 1,300.00 86,938.00
Hasson (rep: 34) Sep 29, '24 at 3:41 am
Thanks for the clarification. I'll try to look at it again tomorrow.
John_Ru (rep: 6417) Sep 29, '24 at 9:03 am
Answers
0
Selected Answer
Hasson
There are two problems with your code:
- the first loop doesn't collect all "Name"s in sheet VB (it misses the last one/row)
- when you write the last Item to your VBA dictionary, you take elements of the array ar2 (which DO NOT sum the columns).
In the attached, revised file, I've modified your code as follows, commenting out some lines and adding comments plus changes in bold:
Sub Combine_Customers()
Dim ar, ar2, a, j As Long
ar = Sheets("CLS").Cells(1).CurrentRegion.Value2
ar2 = Sheets("VB").Cells(1).CurrentRegion
With CreateObject("scripting.dictionary")
'For j = 2 To UBound(ar) - 1
' add all Names to dictionary
For j = 2 To UBound(ar)
.Item(ar(j, 2)) = Array(ar(j, 1), ar(j, 2), ar(j, 3), 0, 0, ar(j, 3))
Next j
For j = 2 To UBound(ar2) ' - 1
If .exists(ar2(j, 2)) Then
a = .Item(ar2(j, 2))
a(3) = a(3) + ar2(j, 4)
a(4) = a(4) + ar2(j, 5)
a(5) = IIf(a(2) = 0, a(3) - a(4), a(2) + a(3) - a(4))
.Item(ar2(j, 2)) = a
Else
.Item(ar2(j, 2)) = Array(ar2(j, 1), ar2(j, 2), 0, ar2(j, 4), ar2(j, 5), ar2(j, 6))
End If
Next j
'.Item(.Count) = Array("TOTAL", "", ar(UBound(ar), 3), ar2(UBound(ar2), 4), ar2(UBound(ar2), 5), Application.Sum(Application.Index(.items, 0, 6)))
' sum all columns. not just Total column
.Item(.Count) = Array("TOTAL", "", _
Application.Sum(Application.Index(.Items, 0, 3)), _
Application.Sum(Application.Index(.Items, 0, 4)), _
Application.Sum(Application.Index(.Items, 0, 5)), _
Application.Sum(Application.Index(.Items, 0, 6)))
Sheets("CLS").Cells(1, 7).Resize(, 6) = Array("DATE", "NAME", "BALANCES", "DEBIT", "CREDIT", "TOTAL")
Sheets("CLS").Cells(2, 7).Resize(.Count, 6) = Application.Index(.Items, 0, 0)
End With
End Sub
If this works for you, please remember to mark this Answer as Selected.