"SUM" Based on Description

[Panoos64]

Hi all, i would like to "SUM" the below numbers using a VBA code which should base on description in col. "AK". In F.1. are the original data and in Sch.2. is the expected result. Therefore that the rows are not stably and are change due to accounts which have movement for each month.
Thanking you in advance

Sch.1.

<tbody>
[TD="class: xl65, width: 128"]AK[/TD]
[TD="class: xl65, width: 65"]AL[/TD]
[TD="class: xl65, width: 71"]AM[/TD]
[TD="class: xl65, width: 76"]AN[/TD]

[TD="align: right"]546[/TD]
[TD="class: xl66"]TOTAL REVENUE:[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66, align: right"]-50,555.00[/TD]

[TD="align: right"]547[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]

[TD="align: right"]548[/TD]
[TD="class: xl65"]COST OF SALES[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]8,150.00[/TD]

[TD="align: right"]549[/TD]
[TD="class: xl65"]ELECTRICIYT[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]3,125.00[/TD]

[TD="align: right"]550[/TD]
[TD="class: xl65"]WATER SUPPLY[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]1,245.00[/TD]

[TD="align: right"]551[/TD]
[TD="class: xl65, colspan: 2"]PRINTING & STATIONERY[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]874.00[/TD]

[TD="align: right"]552[/TD]
[TD="class: xl65, colspan: 2"]HEATING & LIGHTING[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]2,896.00[/TD]

[TD="align: right"]553[/TD]
[TD="class: xl65, colspan: 2"]REPAIRS & MAINTENANCE[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]4,258.00[/TD]

[TD="align: right"]554[/TD]
[TD="class: xl65"]RATES & LICENSES[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]1,368.00[/TD]

[TD="align: right"]555[/TD]
[TD="class: xl65, colspan: 2"]ADMINISTRATION EXPENSES[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]2,562.00[/TD]

</tbody>

Sch.2.

<tbody>
[TD="class: xl65, width: 128"]AK[/TD]
[TD="class: xl65, width: 65"]AL[/TD]
[TD="class: xl65, width: 71"]AM[/TD]
[TD="class: xl65, width: 76"]AN[/TD]

[TD="align: right"]546[/TD]
[TD="class: xl66"]TOTAL REVENUE:[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66, align: right"]-50,555.00[/TD]

[TD="align: right"]547[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]

[TD="align: right"]548[/TD]
[TD="class: xl65"]COST OF SALES[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]8,150.00[/TD]

[TD="align: right"]549[/TD]
[TD="class: xl65"]ELECTRICIYT[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]3,125.00[/TD]

[TD="align: right"]550[/TD]
[TD="class: xl65"]WATER SUPPLY[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]1,245.00[/TD]

[TD="align: right"]551[/TD]
[TD="class: xl65, colspan: 2"]PRINTING & STATIONERY[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]874.00[/TD]

[TD="align: right"]552[/TD]
[TD="class: xl65, colspan: 2"]HEATING & LIGHTING[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]2,896.00[/TD]

[TD="align: right"]553[/TD]
[TD="class: xl65, colspan: 2"]REPAIRS & MAINTENANCE[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]4,258.00[/TD]

[TD="align: right"]554[/TD]
[TD="class: xl65"]RATES & LICENSES[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]1,368.00[/TD]

[TD="align: right"]555[/TD]
[TD="class: xl65, colspan: 2"]ADMINISTRATION EXPENSES[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]2,562.00[/TD]

[TD="align: right"]556[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="align: right"]557[/TD]
[TD="class: xl66"]TOTAL EXPENSES[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66, align: right"]24,478.00[/TD]

[TD="align: right"]558[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="align: right"]559[/TD]
[TD="class: xl66"]NET PROFIT / LOSS[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66, align: right"]-26,077.00[/TD]

</tbody>

 

[Joe4]

Can't you use SUMIF equations to sum based on the description?
See: https://exceljet.net/excel-functions/excel-sumif-function

If you really want/need to use VBA, you can use the function in VBA by calling it like this:

Application.WorksheetFunction.SumIf(...)

 

[Panoos64]

Thank you for advice Joe. but i cannot find the right formula. Is there any easy method to create the above VBA code ? Thanks for your support

 

[Panoos64]

Hi to everyone, any support it would be highly appreciate.

Tks

 

[Joe4]

Are you just trying to get lines 557 and 559?
If not, please describe your requirements in more detail.

 

[Panoos64]

Hi Joe, Please note that the rows ARE NOT FIXED and so i would like to "SUM" at first the expenses, which start from 2nd row below "TOTAL REVENUE:" (1st row is blank) which on above example is the row 548 till the 1st blank row which is 556 and should prepare the sum and the description in col. "AK" "TOTAL EXPENSES" which code should write. Then the "SUM" between "TOTAL REVENUE:" and "TOTAL EXPENSES" and code should write the description "NET PROFIT / LOSS" IN col. "AK"
Thank u so much for ur support

 

[Joe4]

Is a VBA solution acceptable?

 

[Panoos64]

Yeap, i know that there is solution by VBA and no other way can resolve my project and also I know how to create some VBA, by recording macro but the above is little bit complicate and due to that, i am newbie regarding VBA is the reason that i required a support.

In points the code should be as follows:

- Should run through col. "AK" and where find the text "TOTAL REVENUE:" should start "SUM" the below accounts till the next blank row.
- The "SUM" should be placed offset 3 columns in "AN" and parallel should enter the description in col. "AK" "TOTAL EXPENSES".
- Then the code should "SUM" in col. "AN" the rows which contains the descriptions in col "AK" "TOTAL REVENUE:" and "TOTAL EXPENSES", leaving a blank row below text "TOTAL EXPENSES"
- The code should enter description in col."AK" "NET PROFIT / LOSS"

Thank you for your interesting to resolve my project

 

[Joe4]

Try this:

Sub MyMacro()

    Dim row1 As Long
    Dim row2 As Long
    
'   Find "TOTAL REVENUE:" in column AK
    Columns("AK").Find(What:="TOTAL REVENUE:", After:=Range("AK1"), LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
        
'   Capture location of total revenue row
    row1 = ActiveCell.Row
        
'   Find last row of data in section below that
    row2 = Cells(row1 + 2, "AK").End(xlDown).Row
    
'   Insert Total Expenses two rows below
    Cells(row2 + 2, "AK") = "TOTAL EXPENSES"
    Cells(row2 + 2, "AN").Formula = "=SUM(AN" & row1 + 2 & ":AN" & row2 & ")"
    
'   Insert Net Profit/Loss line
    Application.ScreenUpdating = True
    Cells(row2 + 4, "AK") = "NEW PROFIT / LOSS"
    Cells(row2 + 4, "AN") = "=AN" & row1 & " - AN" & row2 + 2
    
End Sub

 

[Panoos64]

Well done Joe!! I appreciated so much for what you done for me. You resolved my project and due to the above i will save many hours, preparing the above report, instead to process it by manual method. Many thanks for you time spent for me and i express my best wishes to you. Have a great lovely day!