Running totals

[Tim_M]

Hello, as you may expect by my posting here, I have an Excel question. This could be something very simple and covered in one of the many excel resource I have available in the office, but I don’t even know where to look. I am working on a construction estimation work sheet for a friend. To make this really simple for explanation purposes, my friend on needs 3 cells each formatted very specifically.
Cell A3 will consist of the last amount paid for a specific item.
Cell B3 will consist of a running total of the amounts entered into Cell A3.

But here is where it gets tricky

I would like Cell A1 to be a control cell with some sort of generic text (the text isn’t important). Every time the text in Cell A1 is changed, the values in Cell A3 are added to Cell B3, and then cell A3 is set to 0 and ready for a new value to be entered, and the next time the control cell is modified the whole process repeats it self.

Thanks,
Tim Mailloux

 

[kskinne]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
[B3].Value = [B3].Value + [A3].Value
[A3].Value = 0
End Sub

this should work for you, assuming that you want this process to happen each time cell A1 is edited, even if the value entered in A1 is the same as the prior entry. also assuming that A3 is a manually entered number and not the result of a formula. let me know if this isn't what you're looking for

hth
kevin

 

[Tim_M]

Kevin,
Thanks a bunch, that works great. Now all I have to do is figure out how to modify the code to make it work on a larger scale. The actual spread sheet will consist of more than just 3 cells. Basicly I want all the values entered into column A to be added to there corresponding total (the adjacent cell) in column B. Then, after the control cell is updated, the totals in Column B are updated and all the values in column A are changed to zero.....I actually tried to spend a little time modifing the code you sent me to do this but I cant seem to get it to work. I probobly should have explained my problem better in more detail the first time, but I though if I gave a scaled down problem, once the answer was presented I could just apply it to the bigger picture. But apparently not in this case.

Thanks
Tim Mailloux
Tecton Architects

 

[kskinne]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
For i = 3 To LastRow
Cells(i, 2).Value = Cells(i, 2).Value + Cells(i, 1).Value
Cells(i, 1).Value = 0
Next i
End Sub

this will do what you want - it is modified so that it adds the amount in column A's row (beginning in row 3 and going to the last row) to the corresponding cell in column B, and then zeroes out the rows in column A. This is still triggered by a change in cell A1, like in the original code.

hth
kevin

 

[Tim_M]

Kevin,
Again I want to thank you for your quick responce, and your code works great. There are a few more questions I would like to ask though. This code work great for the worksheet I am currently creating, but in the future I forsee using this code in other worksheets where the actual columns and rows I need totaled will be different than A & B and will not start in row 3. But the rows will always be next to one another.

Your code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
For i = 3 To LastRow
Cells(i, 2).Value = Cells(i, 2).Value + Cells(i, 1).Value
Cells(i, 1).Value = 0
Next i
End Sub

If I understand this code correctly, the line "For i = 3 to LastRow" specifies that this rotine starts in row 3 and totals the entire column for row 3 down. I could very well specify "For i = 6 to 12" if I wanted, correct? Which would total only rows 6 thru 12.

I also presume that the line "LastRow = Range("A65536").End(xlUp).Row" spcifies the starting point for this function. Say I wanted to have it start in column G, what would I substitue "A65536" with to do that. I simply tried to change the A to a G, but that didn't work.

Thanks
Tim Mailloux
Tecton Architects

 

[kskinne]

Kevin,

If I understand this code correctly, the line "For i = 3 to LastRow" specifies that this rotine starts in row 3 and totals the entire column for row 3 down. I could very well specify "For i = 6 to 12" if I wanted, correct? Which would total only rows 6 thru 12.

I also presume that the line "LastRow = Range("A65536").End(xlUp).Row" spcifies the starting point for this function. Say I wanted to have it start in column G, what would I substitue "A65536" with to do that. I simply tried to change the A to a G, but that didn't work.

Thanks
Tim Mailloux
Tecton Architects

Hi Tim
Actually the line:
For i = 3 to LastRow

specifies that this routine will begin in row 3 and end in the last used row on the sheet.

the line:
"LastRow = Range("A65536").End(xlUp).Row"

tells the macro what the last used row in the sheet is, so it knows where to stop.

for example, if you wanted the code to keep a running total in row H for data you had entered in row G, you would modify the code like this:

Private Sub Worksheet_Change(ByVal Target As Range) 
If Target.Address <> "$A$1" Then Exit Sub 
Dim LastRow As Long 
LastRow = Range("G65536").End(xlUp).Row 
For i = 3 To LastRow 
Cells(i, 8).Value = Cells(i, 8).Value + Cells(i, 7).Value 
Cells(i, 7).Value = 0 
Next i 
End Sub

notice that i changed the lines containing the Cells function. In the original code:

Cells(i, 1) represented the current row that the procedure was in, represented by i, and column 1, or column A.

question, in your code will cell A1 always be the cell that, when edited, will trigger the code - regardless of which column you want the code to run on? if you could give me some more detail about exactly how you want this to work, i am sure we can modify this suit your needs

kevin

 

[Tim_M]

Kevin,
Thank you...again. I think I am all set now. Thanks a million. This is the first time I have used this site and I must say that I am extremly impressed.

Tim Mailloux
Tecton Architects

 

[Tim_M]

Kevin,
I used the VBA code you gave me and applied it to my spread sheet...It works great. But now the person I making this spread sheet for wants a few more bells and whisles and I am stumped. After I created the original work sheet, I was asked to copy it into several new work sheets in the excel file. At this point everything is still working great. But now, the end user wants to be able to change the control cell (the cell that starts the VBA routine) on one worksheet, and have the VBA routine do it's thing on every work sheet.

I tried two things:
1. was to link the control cell in every worksheet back to the one cell the on the first sheet that this guy wants to change to update all sheets...didn't work.

2. I went into the VBA code for every sheet, and changes to the control cell location to sheet 1, cell G5 ('sheet 1'!G%). This didn't work either. Either becuase the code I entered to specify the cell is wronge, or it just wont work that way.

So is this do able?

Tim

 

[kskinne]

hi Tim,

try this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim LastRow As Long

Select Case ActiveSheet.Name
    Case "Sheet1"
    If Target.Address <> "$A$1" Then Exit Sub
    LastRow = Range("G65536").End(xlUp).Row
    For i = 3 To LastRow
    Cells(i, 8).Value = Cells(i, 8).Value + Cells(i, 7).Value
    Cells(i, 7).Value = 0
    Next i

    Case "Sheet2"
    If Target.Address <> "$A$2" Then Exit Sub
    LastRow = Range("G65536").End(xlUp).Row
    For i = 3 To LastRow
    Cells(i, 8).Value = Cells(i, 8).Value + Cells(i, 7).Value
    Cells(i, 7).Value = 0
    Next i
    
    Case "Sheet3"
    If Target.Address <> "$A$3" Then Exit Sub
    LastRow = Range("G65536").End(xlUp).Row
    For i = 3 To LastRow
    Cells(i, 8).Value = Cells(i, 8).Value + Cells(i, 7).Value
    Cells(i, 7).Value = 0
    Next i

    Case Else
    Exit Sub
    
End Select

End Sub

copy this code into the 'this workbook' module in the visual basic editor window, and do away with the other code that is in the sheet module. notice a few things that are different about this code:

1. the first line of the code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

is changed, enabling the code to work in multiple sheets, instead of just one sheet.

2. a select case statement is used in order to tell the code which sheet is currently the active sheet, and then run the appropriate section of code.

3. for each case statement, i made it so that, depending on which sheet you are in, the cell that triggers the code is different. however, the cells that are manipulated by the code in each sheet i left the same, you'll to change that if necessary.

this should do what you need. modify to suit you circumstances, and let me know if you've got any questions.

hth
kevin

 

[Tim_M]

Kevin,

Thank you so much for all of your help thus far. I can't get your code to work however.

I noticed that each sheet in your code was simply labled (sheet1, sheet2, sheet3...). I actually have 21 sheets total in this workbook, and each are named from "Lot 1" up to "Lot 21." I tried applying it to the first few sheets to see if it would work, and tried re-writing the code to fit the need.

Each sheet in this workbook is constructed exactly like the other, and all need to be simultaneously triggered by the change of cell G5 in worksheet #19.

I am hpping to achieve a sort of "roll-over" of this entire project's "previous application," I guess you could say, by changing that one cell to reflect the updated status, or date, of the project.

Is there anymore advice you could give me on this? I would really appreciate it!

Thanks,

Tim