CODEWITHSUNDEEP
excelvbaexcel-formula
Thama
Thama

Reputation: 9

Adding formula in specific row, with specific range

I would like to ask if its possible to adding formula in each row, but with different spesific range in that row

enter image description here

in each yellow cell, I would like to add sum with :

  1. Flora Total = SUM(B2:B3)
  2. Fauna Total = SUM(B5:B9)

Is there any possibility to do that ?

I've tried to define the total of flora and the total of fauna but I don't know to apply the vba for looking up if its flora or its a fauna

Upvotes: 0

Views: 382

Answers (1)

CLR
CLR

Reputation: 12254

You've tagged vba, and you've asked for vba to do this.. so here is a solution using vba. Some (most!) might call this overkill.

The following code scans the sheet and looks for the edges of the table before starting. Because of this, column A and row 1 need to be empty other than for this table - like your screenshot.

It then examines each cell of the table and if the left-most cell ends with the word "Total", it creates a formula to sum up from the last subtotal down to the cell above the formula.

Sub try_this()
    
    Dim wb As Workbook, ws As Worksheet
    Dim x As Long, y as Long, lastrow As Long, lastcolumn As Long
    Dim last_subtotal as long
    
    Set wb = ActiveWorkbook
    Set ws = wb.ActiveSheet
    
    With ws
    
        lastcolumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        For x = 2 To lastcolumn
            last_subtotal = 1
            For y = 2 To lastrow
                If Right(.Cells(y, 1).Value, 5) = "Total" Then
                    .Cells(y, x).Formula2 = "=sum(" & .Cells(last_subtotal + 1, x).Address & ":" & .Cells(y - 1, x).Address & ")"
                    last_subtotal = y
                End If
            Next
        Next
        
    End With

End Sub

Each time it is run, it will overwrite the formula before so can be used to 'correct' formulae that have become incorrect due to a user inserting rows in the wrong place etc. You could even perhaps use an event to ensure they're re-written all the time if you wanted full over-kill.

Upvotes: 1

Related Questions