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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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

montecarlo2012
  • Question Question
change display from horizontal to vertical
Replies
2
Views
137
montecarlo2012
montecarlo2012
J
  • Solved
VBA to merge duplicate rows and sum values in certain column with adjustments
Replies
9
Views
958
Alex Blakenburg
A
F
  • Solved
Web search using Excel VBA error and script corrections
2
Replies
10
Views
237
Edgar_
Edgar_
montecarlo2012
  • Solved
vba=Loop through each row in the data range.(B2:F10000)
Replies
9
Views
2K
montecarlo2012
montecarlo2012
B
  • Solved
VBA to check if worksheet exists
Replies
8
Views
345
bdt
B

Forum statistics

Threads
1,224,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

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