VBA Code works for 1 cell, but need it to work for each row.....please help

[marshen]

Afternoon everyone.
I recently found some great code which allows me to enter a number in cell D2, then when I leave cell D2 the value from D2 is added to C2 and D2 returns to blank, I use this to have a running total in C2.

The issue I have is that I have an ever growing table and want some code that can be applied to every row to do the same thing as above.

Above explains how it works for row 2, however I want the code to basically add the value from column D to the value in column C and then delete the value from column D as I exit the cell and this to work for whichever row I'm on, whether that's row 15 or row 150.

I hope this makes sense and someone can help me.

Below is the code I used for row 2.

Thanks
Mark

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = Range("d2").Address Then
Range("c2") = Range("c2") + Range("d2")
Range("d2").ClearContents
End If
Application.EnableEvents = True
End Sub

 

[Joe4]

Try this:

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

 

[marshen]

Try this:

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

Wow amazing thank you!

If possible a further question please, I also want another calculation based of the same principle.

An example would be, enter a value in F2 and the value to E2 and then remove the value from F2 when I exit the cell.

Unfortunately my VBA knowledge isn't good enough to combine the functions.

Thanks
Mark

 

[marshen]

Wow amazing thank you!

If possible a further question please, I also want another calculation based of the same principle.

An example would be, enter a value in F2 and the value to E2 and then remove the value from F2 when I exit the cell.

Unfortunately my VBA knowledge isn't good enough to combine the functions.

Thanks
Mark

Sorry for the daft question, i've sorted it myself

Thanks for your help

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[marshen]

@Joe4 sorry to trouble you again

I've slightly changed my sheet and want to be able to add an amount in the Last Deposit column and the value populate in Total Deposit column for that row and of course the Last Deposit field then goes blank when the cell is left, however I want to repeat the process for the Last Withdrawal column too, with the value populating in column G.

Are you able to assist me on the code?

The code from yesterday was effecting the whole sheet and not just the required column.

Any assistance is appreciated.

Thanks
Mark

 

[Joe4]

The code from yesterday was effecting the whole sheet and not just the required column.

That is not quite accurate! The code is only affecting entries into column D (the 4th column), as per your requirements.

I've slightly changed my sheet and want to be able to add an amount in the Last Deposit column and the value populate in Total Deposit column for that row and of course the Last Deposit field then goes blank when the cell is left, however I want to repeat the process for the Last Withdrawal column too, with the value populating in column G.

Then you want the code to run when changes are made to column F (the 6th column), or column H (the 8th column).
So you would update my code like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub
    
    If (Target.Column = 6) or (Target.Column = 8) 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