How to add category summation using vba code?-CodePudding

I have a vba code that outputs a list of numbers. This list could vary by every execution. Therefore the number of rows and position of empty cells could change by each run. What I need here is to insert the summation of white cells ( correspond to each product category) into the following green cell at column B (Aval.Qnty). This is showed in the below picture.

Is there any way to do that through piece of vba code?

CodePudding user response：

Add Subtotals

Option Explicit

Sub AddSubTotals()
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' be more specific
    Dim rg As Range: Set rg = ws.Range("A1").CurrentRegion
    Dim rCount As Long: rCount = rg.Rows.Count
    If rCount < 2 Then Exit Sub ' not enough data
    
    With rg.Columns(2)
        
        Dim Data As Variant: Data = .Value
        
        Dim r As Long
        Dim sTotal As Double
        
        For r = 2 To rCount
            If IsEmpty(Data(r, 1)) Then
                .Rows(r).Value = sTotal
                sTotal = 0
            Else
                If IsNumeric(Data(r, 1)) Then
                    sTotal = sTotal   Data(r, 1)
                End If
            End If
        Next r
    
    End With
    
End Sub

CodePudding user response：

(The solution below is not vba but excel formula. It will help you I think.)

You can use the formula at C2 and extend this formula to the bottom to get the sums of product categories:

=IF(LEFT(A2,3)=LEFT(A1,3),B2 C1,B2)

You need to only consider the cells next to the blank cells at column C for sums of product categories.

You can use the formula at D2 and extend this formula to the bottom to get total sum

=IF(RIGHT(A2,1)="X",C2 D1,IF(ISNUMBER(D1),D1,0))

You need to only consider the bottom cell at column D for total sum.

Here is the formulas and result:

Formulas

Result

You can analyze the formulas for how it works.