VBA: Sum cells based on criteria of another column

[NicoleN]

Hi Everyone,
I am fairly new to VBA after recently undertaking (well, still going) an online VBA course. I've used this forum a lot to learn different code, as although the course is fantastic for building foundations, as you all know, it takes a lot more practice than just a short course to learn VBA. So thank you to all the posters out there that give such detailed answers to help people like me!
I have an issue that I haven't seemed to find a relevant answer for...I am wanting to learn the best VBA code for a quite simple task in Excel without VBA, however I need a macro for this as (using a macro) I am copying data from the same workbook into a new worksheet & then re-pasting the numbers as values (original data is formula referencing another worksheet), but I need some data to be new formula. What I would like to do is look up column A and if it has the word 'total' in it, then sum the corresponding values for that row for all cells above (up to the first blank row - like 'autosum' does).
Here's a quick example - so for the rows that have 'total income' and 'total expenditure', I'd like the other 3 values in that row/s to sum the amounts above
[TABLE="width: 310"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Income[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]Account A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Account B[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Account C[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Total Income[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Expenditure[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Account A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Account B[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Account C[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Total Expenditure[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Net Profit[/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]

Thank you!

 

[ask2tsp]

this code must be stored in the code page of the worksheet containing the data. Right-click the sheetlabel and select View code. Paste the following:

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim r As Long, c As Long, total As Double
      
   If Target.Count = 1 And Target.Column = 1 And Target.Row > 1 Then
   If Target.Value Like "Total*" Then
      'sum the values in the rows above this row in all columns
      For c = 2 To Range("A2").End(xlToRight).Column
         total = 0
         For r = Cells(Target.Row - 1, c).End(xlUp).Row _
            To Cells(Target.Row - 1, c).Row
            If r > 1 Then total = total + Cells(r, c).Value
         Next r
         Cells(Target.Row, c) = total
      Next c
   End If
   End If
End Sub

this is an event procedure that runs every time something changes on the worksheet.

 

[ask2tsp]

Here you can set if summing is done in the macro or the sum() formula is written to the cells in the total row

Option Explicit
Option Compare Text

Enum mode
   sumInVba
   writeFormula
End Enum

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim r As Long, c As Long, total As Double, m As mode, _
       firstSumRow As Long, lastSumRow As Long
      
   m = mode.writeFormula
   'm = mode.sumInVba
   
   If Target.Count = 1 And Target.Column = 1 And Target.Row > 1 Then
   If Target.Value Like "Total*" Then
      firstSumRow = Cells(Target.Row - 1, 2).End(xlUp).Row
      If firstSumRow = 1 Then firstSumRow = 2
      lastSumRow = Cells(Target.Row - 1, 2).Row
      
      For c = 2 To Range("A2").End(xlToRight).Column
      Select Case m
         Case mode.sumInVba
            total = 0
            For r = firstSumRow To lastSumRow
               total = total + Cells(r, c).Value
            Next r
            Cells(Target.Row, c) = total
         
         Case mode.writeFormula
            Cells(Target.Row, c).FormulaR1C1 = _
            "=SUM(R" & firstSumRow & "C:R" & lastSumRow & "C)"
            
         End Select
      Next c
   End If
   End If
End Sub

 

[NicoleN]

Thanks so much for your help, but I'm doing something wrong as it's not working for me. When I paste either of your code into VBA I can't actually run the Macro - it's not appearing in the macro list. If I remove the (ByVal Target As Range) then it appears, however it doesn't work as (1) the Target variable is not defined or for (2) it says 'ambiguous name' for Enum mode. Sorry, I know I'm missing something simple but just can't work it out...

 

[ask2tsp]

I understand your confusion.

Did you paste the code in the code module of the wotksheet? It has to go there, not in a standard code module. Either in Excel right-click on the sheet's tab and select View code or in the project explorer in Vbe right-click the sheet name and select view code. Then paste the code there.

This code will execute every time a worksheet_change event occurs. That's why it's not listed in the macro list. The target argument is set by vba to the range that is selected when something changes on the sheet. So if you want to see how it works, change a "Total..." text (or just press F2 Enter on a total... cell).

good luck with that.

 

[NicoleN]

Thanks ask2tsp, that's great! I hadn't seen the worksheet_change event before, bonus learning!