Serious trouble with running total calculations

[dgaburto1]

So I'd like to write a macro that simply calculates the running total for rows of data (not columns) Here's where it gets tricky - I would like to be able to run this for different spreadsheets, and each different speadsheets have different amounts of rows of data. How can I make a macro that calculates the running total based on the condition that the cells have something in it. I am looking for something not hard coded. Been stuck on this for a while. Thanks!

Below is an example of what the data looks like originally, and in the green is how I would like the data to look like after the macro is run.



Rate
4/27/2014
5/4/2014
5/11/2014
5/18/2014
5/25/2014
6/1/2014
6/8/2014
6/15/2014
6/22/2014
6/29/2014




3
4
1
2
2
0
1
1
3
6




4
1
3
1
0
8
4
7
4
2




1
0
1
0
1
3
1
0
0
1

















Desired Running Total
3
7
8
10
12
12
13
14
17
23




4
5
8
9
9
17
21
28
32
34




1
1
2
2
3
6
7
7
7
8

[Bob Phillips]

Public Sub RunningTotals()
Dim lastrow As Long
Dim lastcol As Long
Dim nextrow As Long
Dim i As Long, ii As Long

With ActiveSheet

lastrow = .Range("B1").End(xlDown).Row
lastcol = .Range("A1").End(xlToRight).Column

nextrow = lastrow + 2
.Cells(nextrow, "A").Value = "Running Totals"

For i = 2 To lastrow

For ii = 2 To lastcol

.Cells(nextrow, ii).Value = Application.Sum(.Cells(i, "B").Resize(, ii - 1))
Next ii

nextrow = nextrow + 1
Next i
End With
End Sub

[dgaburto1]

This is exactly what I needed! Your quick help is greatly appreciated. I have one follow up question, is it possible to add a few comments to explain some of the code? I am a beginner and would like to be able to follow the code a little more closely. Thanks again.

[Bob Phillips]

Which bits don't you understand?

[dgaburto1]

Im wondering how and where is the running total calculated, and where could this be changed if I wanted to totals to calculate based off different cell locations. Right now the dates you see on top are being added aswell, I'd like to change it so that the calculations start below the dates

[dgaburto1]

Im also curious if its possible to have the final product (the running total calculations in green) to replace the original data, so that all you would see is the running total. I know this can be done harded, but I was wondering if there was another way? Any ideas would be greatly appreciated.

[Aussiebear]

Im wondering how and where is the running total calculated, and where could this be changed if I wanted to totals to calculate based off different cell locations. Right now the dates you see on top are being added aswell, I'd like to change it so that the calculations start below the dates
How are the dates being "Added"? Given the sample provided I can only assume that any data in rows 1 to 4 are being manually applied to the sheet. Please indicate by way of sample which cells you now think the running totals should be calculated off.


Im also curious if its possible to have the final product (the running total calculations in green) to replace the original data, so that all you would see is the running total. I know this can be done harded, but I was wondering if there was another way? Any ideas would be greatly appreciated.
You could enter the top four rows of data on another sheet and link to the running totals, you could even have the new data entered by way of a form if you wanted.

[dgaburto1]

What I meant by "added' is that for example, date 4/27/14 appears as 42714, and then added to the next cell and so on. I would like the calculations to begin underneath the dates, and replace the already existing data.





Rate
4/27/2014
5/4/2014
5/11/2014
5/18/2014
5/25/2014
6/1/2014
6/8/2014
6/15/2014
6/22/2014
6/29/2014




3
4
1
2
2
0
1
1
3
6




4
1
3
1
0
8
4
7
4
2




1
0
1
0
1
3
1
0
0
1

















Desired Running Total
3
7
8
10
12
12
13
14
17
23




4
5
8
9
9
17
21
28
32
34




1



So, the original numbers in RED would be converted to the numbers you see in green, and replace the original numbers so that only the running totals appear. The dates would stay in "Date" form and wouldn't be touched.

[Bob Phillips]

I assumed that the dates were in row 1, so I processed the data rows in the loops as such


For i = 2 To lastrow

If the dates are in some other row, change the 2 to the dates row + 1.

[Aussiebear]

Given the sample is just an HTML table it is all too easy to assume that the dates are in Row 1. Perhaps it would be better if the example was in a workbook, with individual sheets showing which cells are likely to the ones you wish to calculate from.

[dgaburto1]

Sure, I now need the running totals to appear in place of the original numbers. This macro is part of another macro I have written that filter through different workbooks and converts these numbers to running totals. The amount of rows and columns for each will be different, but the starting place of this data is always the same. Attached I have an example of how the data is presented each time. It is in place of the numbers below the dates and to the right of "desentive" is where I would like the running totals to end up. Any help on this would be great.

[Bob Phillips]

Public Sub RunningTotals()
Const START_ROW As Long = 10
Const START_COL As Long = 6 'column F
Dim lastrow As Long
Dim lastcol As Long
Dim i As Long, ii As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, START_COL).End(xlUp).Row
lastcol = .Cells(START_ROW, START_COL).End(xlToRight).Column

For i = START_ROW + 1 To lastrow

For ii = lastcol To START_COL + 1 Step -1

.Cells(i, ii).Value = Application.Sum(.Cells(i, START_COL).Resize(, ii - START_COL + 1))
Next ii
Next i
End With
End Sub

[dgaburto1]

This works perfectly! Thank you very much, this is exactly what I needed. Made my life a whole lot easier.

I hate to ask another question, but would you mind briefly walking me through the code and briefly explaining what is going on?