If you need the value in column H to keep a running total every time you
enter a new value in column F of the same row (or you could adjust the method
for any 2 cells):
First you need to detect when anything in column F is changed, and some way
of knowing the row. The Worksheet_Change Event Procedure can be used to
detect when there has been a change to any value on the worksheet, so that is
the logical way to do this: put the code in there to do the rest. If you do
not know how to use Event Procedures, I would suggest you ask that in a new
post on this group. I could tell you but it would take more time than I have
now, so perhaps you would get a faster answer through a new inquiry on that.
Now, the code would do the rest, and here is how I would approach it. I
have written it to work even if you copy and paste multiple values at once
into column F (in which case each of those rows need to be updated):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim FRange As Range, ChangedCell As Range
' Find out if Target includes any cells in F:
Set FRange = Intersect(Target, Sheets("Sheet1").Range("F:F"))
' Note: Replace the actual name of the sheet you want checked instead of
"SheetName" in the above
If Not (FRange Is Nothing) Then
' Loop through all the cells in F Range, since these were changed:
For Each ChangedCell In FRange
' Update the value in H (offset 2 columns right) by adding what is in F:
ChangedCell.Offset(0, 2).Value = ChangedCell.Offset(0, 2).Value +
ChangedCell.Value
Next ChangedCell
End If
End Sub
One more thing: You may well have considered this but feel I ought to
mention one weakness of this way of keeping accumulated totals. There is no
way to trace it back - in other words, you can never tell what went into the
total other than the very last value you entered, since all the other values
have been overwritten and are now gone. That may not matter to you, and if
so there is no problem, but felt I ought to point it out since for some
applications it can cause real problems. The usual solution (and one reason
for my confusion earlier!) is to retain prior entries and use a new row for
any new ones, so you have a running list that can keep the running total (but
retaining all prior orders in other rows), with a summary that lists the
current totals for each part (if desired) listed on a separate sheet.
After all the misunderstanding, I hope I finally can be of some help to you!
