I have the following Macro which I got to work how I wanted to for inventory, I would like it to be applied to the entire column instead of having to select each cell. Some cells will be empty and there is a header near the top of the worksheet to ignore. If not possible, being able to have it applied to a selection now, and then as I add more items I can run the macro for that item over time.
Thanks
' The Auto_Open name forces this macro to run every time
' the workbook containing this macro is opened.
Sub Auto_Open()
' Every time a cell's value is changed,
' the RunningTotal macro runs.
Application.OnEntry = "RunningTotal"
End Sub
'----------------------------------------------------------
' This macro runs each time the value of a cell changes.
' It adds the current value of the cell to the value of the
' cell comment. Then it stores the new total in the cell comment.
Sub RunningTotal()
On Error GoTo errorhandler ' Skip cells that have no comment.
With Application.Caller
' Checks to see if the cell is a running total by
' checking to see if the first 4 characters of the cell
' comment are "RT= ". NOTE: there is a space after the equal
' sign.
If Left(.Comment.Text, 4) = "RT= " Then
' Change the cell's value to the new value in the cell
' plus the old total stored in the cell comment.
RT = Right(.Comment.Text, Len(.Comment.Text) - 4) - .Value
.Value = RT
' Store the new total in the cell note.
.Comment.Text Text:="RT= " & RT
End If
End With
Exit Sub ' Skip over the errorhandler routine.
errorhandler: ' End the procedure if no comment in the cell.
Exit Sub
End Sub
'--------------------------------------------------------------
' This macro sets up a cell to be a running total cell.
Sub SetComment()
With ActiveCell
' Set comment to indicate that a running total is present.
' If the ActiveCell is empty, multiplying by 1 will
' return a 0.
.AddComment
.Comment.Text Text:="RT= " & (ActiveCell * 1)
End With
End Sub
Bookmarks