CODEWITHSUNDEEP
excelvba
Jay
Jay

Reputation: 1

Sum totals based on specific value in another column excel VBA

Using VBA within excel, I am trying to add a new row after specific text in column a, then sum the costs that are in column b.

This new row should take the specified text "apple" and add the word total to it in column a. in column b, it should simply sum all the "apple"s.

Column A has a random amount of 3 types of fruits (apples, oranges, and bananas).

Column B has the cost of each apple, orange, and banana.

It should look something like this

Apple $12    
Apple $12    
Apple $12    
orange $13    
orange $13    
Banana $7    
Banana $7

to 

Apple $12    
Apple $12    
Apple $12    
Apple Total $36    
orange $13    
orange $13    
orange Total $26    
Banana $7    
Banana $7    
Banana Total $14

Upvotes: 0

Views: 2386

Answers (1)

Sam
Sam

Reputation: 64

You may want to change the bounds for the loop (I only made it go through row 20). This will work when the values are in currency as well.

Dim i As Integer
Dim j As Integer
Dim frt As String

j = 1

For i = 2 To 20
    If Cells(i, 1).Value <> Cells(i - 1, 1).Value Then
        Range("A" & i).EntireRow.Insert
        frt = Cells(i - 1, 1).Value & " Total"
        Cells(i, 1).Value = frt
        Cells(i, 2).Value = Application.WorksheetFunction.Sum(Range("B" & j, "B" & i - 1))
        j = i + 1
        i = i + 1
    End If
Next i

End Sub

Upvotes: 0

Related Questions