Running Totals
Posted by Teri Fieds on January 07, 2002 5:21 PM
Hi, I need help with a running total. I have a spreadsheet with hours worked and overtime hours (something like this)
Name: Fields, Total OT Hours: 170, Current OT Hours: 8, New Total OT Hour: 178. I need a macro (I think) to save the total hours(178) and move it the the total OT column and then allow me to add additional hours to the current hours cell. A sort of running or cummulative total, can this be done? Maybe my approach is wrong. I wonder if anyone has ever had a problem like this. Thanks in advance.
Posted by Bariloche on January 07, 2002 10:56 PM
Teri,
Copy the two subroutines below. Then right click on the sheet tab of your OT log, click on "View Code" and paste these subroutines in the code sheet that comes up.
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Selection.Columns.Count > 1 Then MsgBox "Please select cells in one column only."
ActiveCell.Select
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim intColumn As Integer
Dim intRow As Integer
Dim CellContents as Double
For Each cell In Target
intColumn = cell.Column
intRow = cell.Row
If IsNumeric(cell.Value) Then
Application.EnableEvents = False
CellContents = Cells(intRow, intColumn).Value
Cells(intRow, intColumn + 1).Value = Cells(intRow, intColumn + 1).Value + CellContents
Application.EnableEvents = True
End If
Next cell
End Sub
Now, whenever you enter a number in a cell it will be added to whatever value is in the cell just to the right of it. If you want to add numbers by highlighting a bunch of cells in a column, entering the number and pressing Ctrl+Enter (for instance, if everybody worked 2 hrs OT) you can do that and this code won't mind. The first subroutine prevents someone from inadvertantly trying to enter data in more than one column at a time (if this causes a problem, yell, and I'll tweak it to accommodate your needs; within reason of course :-)) )
Hopefully I've interpreted your request correctly. If not, yell about that too. :-))
have fun
