PDA

View Full Version : Need VBA code to total the cells above rolling the total to the next summation



tmarinho
06-20-2017, 08:06 AM
Very new to VBA and trying to learn as much as i can on the fly. Need some serious help with this!

I have a file that pulls all the changes made during a certain period and sorts them based on date and what it is assigned too (budget, outlook03, outlook05, etc.) I would like to be able to automatically sum up the changes to get me a total for each assigned outlook. For example: I start at 100 there are 3 different changes (3 different rows) for Outlook02 the total would be 100+the 3 rows. Then i want to use that last total and add the next few changes to get the total for outlook03 and so on.

Below i have an example of my sheet. I need to automatically sum the starting budget number of 31 and all the outlook03 changes to get a total Outlook03. I then need to use that new total and add the changes of outlook07 to get the total of Outlook07 and so on. I need to do this for columns G to AX.

the number of changes in any given outlook will vary. I couldn't think of vba code that would work for this. Please help if this is at all possible.






a
b
c
d
e
f
g
h
i



Budget






31.0
35.0



Date
Category
Change Type
Detailed Comments
Assign Change to
Step

Jan-17
Feb-17



3/7/2017
Other/Timing
AHT
work
Outlook03


1.0
1.0



3/15/2017
Lost Efficiencies
Driver
ok it has too
Outlook03


1.0
1.0



3/29/2017
Efficiency Initiatives
Frequency
blank
Outlook03


1.0
1.0



4/5/2017
Volume
AWOP
blank2
Outlook03


1.0
1.0



4/26/2017
Efficiency Initiatives
AHT
last one
Outlook03


1.0
1.0







TOTAL OUTLOOK03







4/26/2017
Invest
AHT
gftrgsf
Outlook07


3.0
4.0

mdmackillop
06-20-2017, 09:10 AM
Can you post a sample workbook showing data and expected outcome. Go Advanced/Manage Attachments

tmarinho
06-20-2017, 10:11 AM
I attached an example workbook. The first tab is what I'm working with, the second is what i need it to look like.

mdmackillop
06-20-2017, 11:22 AM
This assumes that the Budget Row exists in the target worksheet (Sheet2)

Option Explicit


Sub Test()
Dim wsS As Worksheet
Dim wsT As Worksheet
Dim Tgt As Range
Dim Rng As Range, R1 As Range, R2 As Range
Dim Tot As Range
Dim Rw As Long, Cel As Range

Set wsS = Sheets("Sheet1")
Set wsT = Sheets("Sheet2")

With wsSs
Set R1 = .Columns(1).Find("Start").Offset(1)
Set R2 = .Cells(Rows.Count, 1).End(xlUp)
End With

Set Rng = Range(R1, R2).Resize(, 20)

Set Tgt = wsT.Cells(Rows.Count, 1).End(xlUp)(2)
Rng.Copy Tgt
Rw = Tgt(0).Row
Set R1 = Tgt.Offset(1)
Set R2 = wsT.Cells(Rows.Count, 1).End(xlUp)(2)
Set Rng = Range(R1, R2).Resize(, 20)
Set Tot = Rng.Columns(5).SpecialCells(xlCellTypeBlanks)

For Each Cel In Tot.Cells
Cel.Value = Cel(0) & " Total"
Cel.Offset(, 3).Resize(, 13).FormulaR1C1 = "=SUBTOTAL(9," & "R" & Rw & "C:RC)"
With Cel.Offset(, -4).Resize(, 20)
.Font.Bold = True
.Interior.Color = 13434879
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
End With
Next Cel
End Sub