Modify VBA code to prepare "SUM' with existing a/cs

P

Panoos64

Well-known Member
Joined
Mar 1, 2014
Messages
890
Hi all, kindly require to modify the below code so that to still prepare the "SUM" if one or more below accounts are missing.
e.g. if the row in col. "A" with account description "FIXED EXPENSES" & "SUNDRY EXPENSES are missing, the code should run and prepare the "SUM" with the rest of them.

Thanks in advance


Dim Nams As Variant, n As Variant, c As long, oSum(1 To 5) As Double
Dim Rng As Range, Dn As Range, Dic As Object
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
Nams = Array("TOTAL OPERATING EXP.", "COST OF SALES", "DIRECT EXPENSES", "FIXED EXPENSES", "ADMINISTRATION EXPENSES", "SUNDRY ACCOUNTS", "SUNDRY INCOME", "SUNDRY EXPENSES", "MISCELLENEOUS")
Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
For Each n In Nams: Dic(n) = Empty: Next
For Each Dn In Rng
If Dic.Exists(Dn.Value) Then
oSum(1) = oSum(1) + Dn.Offset(, 1).Value
oSum(2) = oSum(2) + Dn.Offset(, 3).Value
oSum(3) = oSum(3) + Dn.Offset(, 5).Value
oSum(4) = oSum(4) + Dn.Offset(, 7).Value
oSum(5) = oSum(5) + Dn.Offset(, 9).Value
c = c + 1
End If
If c = Dic.Count Then
With Dn.Offset(1)
.Resize(2).EntireRow.Insert
.Offset(-1).Value = "TOTAL EXPENSES"
.Offset(-1, 1).Value = oSum(1)
.Offset(-1, 3).Value = oSum(2)
.Offset(-1, 5).Value = oSum(3)
.Offset(-1, 7).Value = oSum(4)
.Offset(-1, 9).Value = oSum(5)
End With
Exit Sub
End If
Next Dn

End Sub
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Try changing:-
This line:

Code: 
If c = Dic.Count Then

To this line:-
Code: 
If c = Rng.Count Then
 
Upvote 0
Hi Mick, is still not working. It appears a msg as follow:

Run-time error '13':
Type mismatch

Thank you for your support
 
Upvote 0
Hi Mick, the "TOTAL EXPENSES" is the result of TOTAL OPERATING EXP., DIRECT EXPENSES, FIXED EXPENSES and ADMINISTRATION EXPENSES. there is no COST OF SALES or SUNDRY EXPENSES e.t.c. but the code should run too with existing a/cs

Thanks you once again for your support. Hv a great day


ORIGINAL DATA



<colgroup><col style="mso-width-source:userset;mso-width-alt:768;width:16pt" width="22"> <col style="mso-width-source:userset;mso-width-alt:6769;width:143pt" width="190"> <col style="mso-width-source:userset;mso-width-alt:2190;width:46pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:1251;width:26pt" width="35"> <col style="mso-width-source:userset;mso-width-alt:2019;width:43pt" width="57"> <col style="mso-width-source:userset;mso-width-alt:1166;width:25pt" width="33"> <col style="mso-width-source:userset;mso-width-alt:2076;width:44pt" width="58"> <col style="mso-width-source:userset;mso-width-alt:967;width:20pt" width="27"> <col style="mso-width-source:userset;mso-width-alt:2616;width:55pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:881;width:19pt" width="25"> <col style="mso-width-source:userset;mso-width-alt:1991;width:42pt" width="56"> <col style="mso-width-source:userset;mso-width-alt:1336;width:28pt" width="38"> </colgroup><tbody>
[TD="class: xl65, width: 22"][/TD]
[TD="class: xl66, width: 190"]A[/TD]
[TD="class: xl66, width: 62"]B[/TD]
[TD="class: xl67, width: 35"]C[/TD]
[TD="class: xl66, width: 57"]D[/TD]
[TD="class: xl67, width: 33"]E[/TD]
[TD="class: xl66, width: 58"]F[/TD]
[TD="class: xl67, width: 27"]G[/TD]
[TD="class: xl66, width: 74"]H[/TD]
[TD="class: xl67, width: 25"]I[/TD]
[TD="class: xl66, width: 56"]J[/TD]
[TD="class: xl67, width: 38"]K[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl69"]T.M.[/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl69"]L.Y.M.[/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl69"]T.Y.[/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl69"]L.Y.[/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl69"]VARIANCE[/TD]
[TD="class: xl70"] [/TD]

[TD="class: xl65, align: right"]40[/TD]
[TD="class: xl71"] TOTAL OPERATING EXP.
 [/TD]
[TD="class: xl72, align: right"] 75,100 [/TD]
[TD="class: xl73"][/TD]
[TD="class: xl72, align: right"] 78,265 [/TD]
[TD="class: xl73"][/TD]
[TD="class: xl72, align: right"] 800,125 [/TD]
[TD="class: xl73"][/TD]
[TD="class: xl72, align: right"] 755,458 [/TD]
[TD="class: xl73"][/TD]
[TD="class: xl72, align: right"] 44,667 [/TD]
[TD="class: xl74"][/TD]

[TD="class: xl65, align: right"]41[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]

[TD="class: xl65, align: right"]42[/TD]
[TD="class: xl71"] G.O.P
 [/TD]
[TD="class: xl72, align: right"] 35,205 [/TD]
[TD="class: xl73"][/TD]
[TD="class: xl72, align: right"] 38,474 [/TD]
[TD="class: xl73"][/TD]
[TD="class: xl72, align: right"] 250,148 [/TD]
[TD="class: xl73"][/TD]
[TD="class: xl72, align: right"] 260,365 [/TD]
[TD="class: xl73"][/TD]
[TD="class: xl72, align: right"] (10,217) [/TD]
[TD="class: xl74"] [/TD]

[TD="class: xl65, align: right"]43[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]

[TD="class: xl65, align: right"]44[/TD]
[TD="class: xl68"]RATES & LICENSES[/TD]
[TD="class: xl79"] [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]1,000 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]5,150 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]7,200 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"](2,050)[/TD]
[TD="class: xl80"] [/TD]

[TD="class: xl65, align: right"]45[/TD]
[TD="class: xl68"]ELECTRICITY[/TD]
[TD="class: xl79, align: right"]75 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79"] [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]5,147 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]6,555 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"](1,408)[/TD]
[TD="class: xl80"] [/TD]

[TD="class: xl65, align: right"]46[/TD]
[TD="class: xl68"]INSURANCES[/TD]
[TD="class: xl79"] [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]250 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]2,200 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]3,500 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"](1,300)[/TD]
[TD="class: xl80"] [/TD]

[TD="class: xl65, align: right"]47[/TD]
[TD="class: xl68"]COMMISSIONS[/TD]
[TD="class: xl81, align: right"]45 [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl81, align: right"]105 [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl81, align: right"]450 [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl81, align: right"]355 [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl79, align: right"]95 [/TD]
[TD="class: xl82"] [/TD]

[TD="class: xl65, align: right"]48
[/TD]
[TD="class: xl83"] DIRECT EXPENSES
 [/TD]
[TD="class: xl84, align: right"] 120 [/TD]
[TD="class: xl84"][/TD]
[TD="class: xl84, align: right"] 1,355 [/TD]
[TD="class: xl84"][/TD]
[TD="class: xl84, align: right"] 12,947 [/TD]
[TD="class: xl84"][/TD]
[TD="class: xl84, align: right"] 17,610 [/TD]
[TD="class: xl84"][/TD]
[TD="class: xl84, align: right"] (4,663) [/TD]
[TD="class: xl85"] [/TD]

[TD="class: xl65, align: right"]49[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"]
[/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]

[TD="class: xl65, align: right"]50[/TD]
[TD="class: xl68"]MANAGEMENT FEES
[/TD]
[TD="class: xl79, align: right"]150 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]258 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]6,555 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]5,459 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]1,096 [/TD]
[TD="class: xl80"] [/TD]

[TD="class: xl65, align: right"]51[/TD]
[TD="class: xl68"]AUDIT FEES[/TD]
[TD="class: xl79"] [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]147 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]5,147 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]4,258 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]889 [/TD]
[TD="class: xl80"] [/TD]

[TD="class: xl65, align: right"]52[/TD]
[TD="class: xl68"]PROFESSIONAL FEES[/TD]
[TD="class: xl79, align: right"]268 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]254 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]3,725 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]4,178 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"](453)[/TD]
[TD="class: xl80"] [/TD]

[TD="class: xl65, align: right"]53[/TD]
[TD="class: xl68"]SUNDRY ADMIN EXPENSES[/TD]
[TD="class: xl81"] [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl81"] [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl81, align: right"]1,785 [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl81, align: right"]1,955 [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl79, align: right"](170)[/TD]
[TD="class: xl82"] [/TD]

[TD="class: xl65, align: right"]54
[/TD]
[TD="class: xl83"] FIXED EXPENSES
 [/TD]
[TD="class: xl84, align: right"] 418 [/TD]
[TD="class: xl84"][/TD]
[TD="class: xl84, align: right"] 659 [/TD]
[TD="class: xl84"][/TD]
[TD="class: xl84, align: right"] 17,212 [/TD]
[TD="class: xl84"][/TD]
[TD="class: xl84, align: right"] 15,850 [/TD]
[TD="class: xl84"][/TD]
[TD="class: xl84, align: right"] 1,362 [/TD]
[TD="class: xl85"] [/TD]

[TD="class: xl65, align: right"]55[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl75"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl75"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl75"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl75"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl75"][/TD]
[TD="class: xl76"] [/TD]

[TD="class: xl65, align: right"]56[/TD]
[TD="class: xl71"] N.P.B.F.E
 [/TD]
[TD="class: xl72, align: right"] 34,667 [/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72, align: right"] 36,460 [/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72, align: right"] 219,989 [/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72, align: right"] 226,905 [/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72, align: right"] (6,916) [/TD]
[TD="class: xl72"] [/TD]

[TD="class: xl65, align: right"]57[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]

[TD="class: xl65, align: right"]58[/TD]
[TD="class: xl68"]BANK CHARGES[/TD]
[TD="class: xl79, align: right"]75 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]90 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]365 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]405 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"](40)[/TD]
[TD="class: xl80"] [/TD]

[TD="class: xl65, align: right"]59[/TD]
[TD="class: xl68"]INTEREST CHARGES[/TD]
[TD="class: xl81, align: right"]158 [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl81, align: right"]160 [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl81, align: right"]748 [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl81, align: right"]810 [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl79, align: right"](62)[/TD]
[TD="class: xl82"] [/TD]

[TD="class: xl65, align: right"]60[/TD]
[TD="class: xl83"] ADMINISTRATION EXPENSES
 [/TD]
[TD="class: xl84, align: right"] 233 [/TD]
[TD="class: xl84"][/TD]
[TD="class: xl84, align: right"] 250 [/TD]
[TD="class: xl84"][/TD]
[TD="class: xl84, align: right"] 1,113 [/TD]
[TD="class: xl84"][/TD]
[TD="class: xl84, align: right"] 1,215 [/TD]
[TD="class: xl84"][/TD]
[TD="class: xl84, align: right"] (102) [/TD]
[TD="class: xl85"] [/TD]

[TD="class: xl65, align: right"]61[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]

[TD="class: xl65, align: right"]62[/TD]
[TD="class: xl86"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl87"] [/TD]

[TD="class: xl65, align: right"]63[/TD]
[TD="class: xl71"] N. P & L
[/TD]
[TD="class: xl72, align: right"] 34,434 [/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72, align: right"] 36,210 [/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72, align: right"] 218,876 [/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72, align: right"] 225,690 [/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72, align: right"] (6,814) [/TD]
[TD="class: xl72"]
[/TD]

</tbody>


EXPECTED RESULT


<colgroup><col style="mso-width-source:userset;mso-width-alt:768;width:16pt" width="22"> <col style="mso-width-source:userset;mso-width-alt:6769;width:143pt" width="190"> <col style="mso-width-source:userset;mso-width-alt:2190;width:46pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:1251;width:26pt" width="35"> <col style="mso-width-source:userset;mso-width-alt:2019;width:43pt" width="57"> <col style="mso-width-source:userset;mso-width-alt:1166;width:25pt" width="33"> <col style="mso-width-source:userset;mso-width-alt:2076;width:44pt" width="58"> <col style="mso-width-source:userset;mso-width-alt:967;width:20pt" width="27"> <col style="mso-width-source:userset;mso-width-alt:2616;width:55pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:881;width:19pt" width="25"> <col style="mso-width-source:userset;mso-width-alt:1991;width:42pt" width="56"> <col style="mso-width-source:userset;mso-width-alt:1336;width:28pt" width="38"> </colgroup><tbody>
[TD="class: xl65, width: 22"][/TD]
[TD="class: xl66, width: 190"]A[/TD]
[TD="class: xl66, width: 62"]B[/TD]
[TD="class: xl67, width: 35"]C[/TD]
[TD="class: xl66, width: 57"]D[/TD]
[TD="class: xl67, width: 33"]E[/TD]
[TD="class: xl66, width: 58"]F[/TD]
[TD="class: xl67, width: 27"]G[/TD]
[TD="class: xl66, width: 74"]H[/TD]
[TD="class: xl67, width: 25"]I[/TD]
[TD="class: xl66, width: 56"]J[/TD]
[TD="class: xl67, width: 38"]K[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl69"]T.M.[/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl69"]L.Y.M.[/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl69"]T.Y.[/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl69"]L.Y.[/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl69"]VARIANCE[/TD]
[TD="class: xl70"] [/TD]

[TD="class: xl65, align: right"]40
[/TD]
[TD="class: xl71"] TOTAL OPERATING COST
 [/TD]
[TD="class: xl72, align: right"] 75,100 [/TD]
[TD="class: xl73"][/TD]
[TD="class: xl72, align: right"] 78,265 [/TD]
[TD="class: xl73"][/TD]
[TD="class: xl72, align: right"] 800,125 [/TD]
[TD="class: xl73"][/TD]
[TD="class: xl72, align: right"] 755,458 [/TD]
[TD="class: xl73"][/TD]
[TD="class: xl72, align: right"] 44,667 [/TD]
[TD="class: xl74"] [/TD]

[TD="class: xl65, align: right"]41[/TD]
[TD="class: xl68"]
[/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]

[TD="class: xl65, align: right"]42[/TD]
[TD="class: xl71"] G.O.P
 [/TD]
[TD="class: xl72, align: right"] 35,205 [/TD]
[TD="class: xl73"][/TD]
[TD="class: xl72, align: right"] 38,474 [/TD]
[TD="class: xl73"][/TD]
[TD="class: xl72, align: right"] 250,148 [/TD]
[TD="class: xl73"][/TD]
[TD="class: xl72, align: right"] 260,365 [/TD]
[TD="class: xl73"][/TD]
[TD="class: xl72, align: right"] (10,217) [/TD]
[TD="class: xl74"] [/TD]

[TD="class: xl65, align: right"]43[/TD]
[TD="class: xl68"]
[/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]

[TD="class: xl65, align: right"]44[/TD]
[TD="class: xl68"]RATES & LICENSES[/TD]
[TD="class: xl79"] [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]1,000 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]5,150 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]7,200 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"](2,050)[/TD]
[TD="class: xl80"] [/TD]

[TD="class: xl65, align: right"]45[/TD]
[TD="class: xl68"]ELECTRICITY[/TD]
[TD="class: xl79, align: right"]75 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79"] [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]5,147 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]6,555 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"](1,408)[/TD]
[TD="class: xl80"] [/TD]

[TD="class: xl65, align: right"]46[/TD]
[TD="class: xl68"]INSURANCES[/TD]
[TD="class: xl79"] [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]250 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]2,200 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]3,500 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"](1,300)[/TD]
[TD="class: xl80"] [/TD]

[TD="class: xl65, align: right"]47[/TD]
[TD="class: xl68"]COMMISSIONS[/TD]
[TD="class: xl81, align: right"]45 [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl81, align: right"]105 [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl81, align: right"]450 [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl81, align: right"]355 [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl79, align: right"]95 [/TD]
[TD="class: xl82"] [/TD]

[TD="class: xl65, align: right"]48[/TD]
[TD="class: xl83"] DIRECT EXPENSES
 [/TD]
[TD="class: xl84, align: right"] 120 [/TD]
[TD="class: xl84"][/TD]
[TD="class: xl84, align: right"] 1,355 [/TD]
[TD="class: xl84"][/TD]
[TD="class: xl84, align: right"] 12,947 [/TD]
[TD="class: xl84"][/TD]
[TD="class: xl84, align: right"] 17,610 [/TD]
[TD="class: xl84"][/TD]
[TD="class: xl84, align: right"] (4,663) [/TD]
[TD="class: xl85"] [/TD]

[TD="class: xl65, align: right"]49[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]

[TD="class: xl65, align: right"]50[/TD]
[TD="class: xl68"]MANAGEMENT FEES[/TD]
[TD="class: xl79, align: right"]150 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]258 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]6,555 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]5,459 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]1,096 [/TD]
[TD="class: xl80"] [/TD]

[TD="class: xl65, align: right"]51[/TD]
[TD="class: xl68"]AUDIT FEES[/TD]
[TD="class: xl79"] [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]147 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]5,147 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]4,258 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]889 [/TD]
[TD="class: xl80"] [/TD]

[TD="class: xl65, align: right"]52[/TD]
[TD="class: xl68"]PROFESSIONAL FEES[/TD]
[TD="class: xl79, align: right"]268 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]254 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]3,725 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]4,178 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"](453)[/TD]
[TD="class: xl80"] [/TD]

[TD="class: xl65, align: right"]53[/TD]
[TD="class: xl68"]SUNDRY ADMIN EXPENSES[/TD]
[TD="class: xl81"] [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl81"] [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl81, align: right"]1,785 [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl81, align: right"]1,955 [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl79, align: right"](170)[/TD]
[TD="class: xl82"] [/TD]

[TD="class: xl65, align: right"]54[/TD]
[TD="class: xl83"] FIXED EXPENSES
 [/TD]
[TD="class: xl84, align: right"] 418 [/TD]
[TD="class: xl84"][/TD]
[TD="class: xl84, align: right"] 659 [/TD]
[TD="class: xl84"][/TD]
[TD="class: xl84, align: right"] 17,212 [/TD]
[TD="class: xl84"][/TD]
[TD="class: xl84, align: right"] 15,850 [/TD]
[TD="class: xl84"][/TD]
[TD="class: xl84, align: right"] 1,362 [/TD]
[TD="class: xl85"] [/TD]

[TD="class: xl65, align: right"]55[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]

[TD="class: xl65, align: right"]56[/TD]
[TD="class: xl71"] N.P.B.F.E
 [/TD]
[TD="class: xl72, align: right"] 34,667 [/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72, align: right"] 36,460 [/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72, align: right"] 219,989 [/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72, align: right"] 226,905 [/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72, align: right"] (6,916) [/TD]
[TD="class: xl72"] [/TD]

[TD="class: xl65, align: right"]57[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl77"] [/TD]
[TD="class: xl78"] [/TD]

[TD="class: xl65, align: right"]58[/TD]
[TD="class: xl68"]BANK CHARGES[/TD]
[TD="class: xl79, align: right"]75 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]90 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]365 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"]405 [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl79, align: right"](40)[/TD]
[TD="class: xl80"] [/TD]

[TD="class: xl65, align: right"]59[/TD]
[TD="class: xl68"]INTEREST CHARGES[/TD]
[TD="class: xl81, align: right"]158 [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl81, align: right"]160 [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl81, align: right"]748 [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl81, align: right"]810 [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl79, align: right"](62)[/TD]
[TD="class: xl82"] [/TD]

[TD="class: xl65, align: right"]60[/TD]
[TD="class: xl83"] ADMINISTRATION EXPENSES
 [/TD]
[TD="class: xl84, align: right"] 233 [/TD]
[TD="class: xl84"][/TD]
[TD="class: xl84, align: right"] 250 [/TD]
[TD="class: xl84"][/TD]
[TD="class: xl84, align: right"] 1,113 [/TD]
[TD="class: xl84"][/TD]
[TD="class: xl84, align: right"] 1,215 [/TD]
[TD="class: xl84"][/TD]
[TD="class: xl84, align: right"] (102) [/TD]
[TD="class: xl85"] [/TD]

[TD="class: xl65, align: right"]61[/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl91"] [/TD]
[TD="class: xl91"] [/TD]
[TD="class: xl91"] [/TD]
[TD="class: xl91"] [/TD]
[TD="class: xl91"] [/TD]
[TD="class: xl91"] [/TD]
[TD="class: xl91"] [/TD]
[TD="class: xl91"] [/TD]
[TD="class: xl91"] [/TD]
[TD="class: xl92"] [/TD]

[TD="class: xl65, align: right"]62[/TD]
[TD="class: xl90"] TOTAL EXPENSES
[/TD]
[TD="class: xl91, align: right"] 75,871 [/TD]
[TD="class: xl91"] [/TD]
[TD="class: xl91, align: right"] 80,529 [/TD]
[TD="class: xl91"] [/TD]
[TD="class: xl91, align: right"] 831,397 [/TD]
[TD="class: xl91"] [/TD]
[TD="class: xl91, align: right"] 790,133 [/TD]
[TD="class: xl91"] [/TD]
[TD="class: xl91, align: right"] 41,264 [/TD]
[TD="class: xl91"] [/TD]

[TD="class: xl65, align: right"]63[/TD]
[TD="class: xl86"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl76"] [/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl87"] [/TD]

[TD="class: xl65, align: right"]64
[/TD]
[TD="class: xl71"] N. P & L
[/TD]
[TD="class: xl72, align: right"] 34,434 [/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72, align: right"] 36,210 [/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72, align: right"] 218,876 [/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72, align: right"] 225,690 [/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72, align: right"] (6,814) [/TD]
[TD="class: xl72"] [/TD]

</tbody>
 
Upvote 0
Give this a Try:-
NB:- I assumed the data starts at row 40.
Code: 
[COLOR="Navy"]Sub[/COLOR] MG24Aug42
im Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Nams [COLOR="Navy"]As[/COLOR] Variant, nRng [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range, Rw [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A40", Range("A" & Rows.Count).End(xlUp))
Nams = Array("TOTAL OPERATING EXP.", "COST OF SALES", "DIRECT EXPENSES", "FIXED EXPENSES", "ADMINISTRATION EXPENSES", "SUNDRY ACCOUNTS", "SUNDRY INCOME", "SUNDRY EXPENSES", "MISCELLENEOUS")
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Nams): .Item(Nams(n)) = Empty: [COLOR="Navy"]Next[/COLOR]
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Dn Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
 
    ReDim Ray(1 To 10)
    Ray(1) = "Total Expences"
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] nRng
            [COLOR="Navy"]For[/COLOR] n = 2 To 10 [COLOR="Navy"]Step[/COLOR] 2
                Ray(n) = Ray(n) + R.Offset(, n - 1).Value
            [COLOR="Navy"]Next[/COLOR] n
        [COLOR="Navy"]Set[/COLOR] Rw = R
        [COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]With[/COLOR] Rw.Offset(2)
    .EntireRow.Insert
         [COLOR="Navy"]With[/COLOR] .Resize(, UBound(Ray))
            .Value = Ray
            .Font.Color = vbRed
            .NumberFormat = "#,##0"
        [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thank u Mick. It works perfect now. Many thanks also for your time spent for my project. Hv a great day
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
VBA to merge duplicate rows and sum values in certain column with adjustments
Replies
9
Views
875
Alex Blakenburg
A
montecarlo2012
  • Solved
vba=Loop through each row in the data range.(B2:F10000)
Replies
9
Views
2K
montecarlo2012
montecarlo2012
S
  • Question Question
Using Defined name and/or direct formula in VBA
2
Replies
11
Views
413
Schturman
S
M
  • Question Question
how change code from range to TABLE and from sheet to multiple
Replies
8
Views
589
Maklil
M
M
  • Question Question
select value two columns to the left of variable in an array
Replies
1
Views
274
myall_blues
myall_blues

Forum statistics

Threads
1,223,909
Messages
6,175,313
Members
452,634
Latest member
cpostell

Share this page

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
Back
Top