VBA code for running total

[marshen]

Hi all.
I had some help yesterday from this great community but I need a little more help if possible.

I want to be able to add a value in column F and when when I exit the cell in column F the value that was input in to that cell is then added to the value in column E on the same row, the idea being it will create a running total for that particular row.

The key things to note is that I want to be able to do this for several columns on the same sheet, so input in to F and add to E, input in to H and add to G, input in to P and add to O and input in to R and add to Q.

Every time the user exists columns F,H,P or R the value from that cell needs to be cleared from that cell so it's ready for another value to be entered.

Please note that I want the sheet to be locked so that the user can only enter values in columns F,H,P or R.

I did have the code below which was adding the value to the column to the left, however it was doing it for every column in the sheet and not just the required column, also it didn't work when the sheet was locked.

Any advice on sorting this would be greatly appreciated.

Thanks
Mark

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.CountLarge > 1 Then Exit Sub

If Target.Column = 4 Then
Application.EnableEvents = False
Target.Offset(0, -1).Value = Target.Offset(0, -1).Value + Target.Value
Target.ClearContents
Application.EnableEvents = True
End If

End Sub

 

[Legacy 143009]

I assume you are looking something like this:

Dim tempValue As Variant
Dim tempAddress As Range

Private Sub Worksheet_Change(ByVal Target As Range)
  Set tempAddress = Target
  tempValue = Target.Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Not Intersect(tempAddress, Range("F:F")) Is Nothing Or Not Intersect(tempAddress, Range("H:H")) Is Nothing Or Not Intersect(tempAddress, Range("P:P")) Is Nothing Or Not Intersect(tempAddress, Range("R:R")) Is Nothing Then
   Cells(tempAddress.Row, tempAddress.Column - 1).Value = Cells(tempAddress.Row, tempAddress.Column - 1).Value + tempValue
  End If
End Sub

 

[HongRu]

First, make sure your column F, H, P, R are not locked. Or run the macro:

Sub unlocked()
    Range("F:F,H:H,P:P,R:R").Locked = False
End Sub

The code I suggest:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    
    If Not Intersect(Target, Range("F:F,H:H,P:P,R:R")) Is Nothing Then
        Application.EnableEvents = False
        Unprotect Password:=""
        Target.Offset(0, -1) = Target.Offset(0, -1) + Target
        Target.ClearContents
        Protect Password:=""
        Application.EnableEvents = True
    End If
End Sub

 

[Legacy 143009]

I want to be able to add a value in column F and when when I exit the cell in column F the value that was input in to that cell is then added to the value in column E on the same row, the idea being it will create a running total for that particular row.

@HongRu I used SelectionChange event because user is asking to sum values on exit, not on change.
On the other hand your range shorthand makes the code readable

If Not Intersect(Target, Range("F:F,H:H,P:P,R:R")) Is Nothing Then

 

[marshen]

Thank you for all the replies, it is really appreciated!

The code from *HongRu has worked exactly how i wanted

Thanks again everyone