Enter a value in one cell and subtract from another without formula

[wilkisa]

My user currently has a range of cells D7:D14. There are no formulas in these cells, just values. He wants to input a number in the corresponding cell in column F, hit ENTER, and have this number subtracted from the cell in column D, and display the result. He also wants the cell in column F to go blank so it can have another entry at a future date.

Example: D7 contains the number 28. He types in the number 5 in cell F7 and hits ENTER. D7 should change to number 23 and cell F7 should now be blank.

Can someone help with this? I'm sure it has to be done in code.

Thanks!
Shirlene

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F7:F14")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Target
    .Offset(, -2).Value = .Offset(, -2).Value - .Value
    .ClearContents
End With
Application.EnableEvents = True
End Sub

then press ALT + Q to close the code window.

 

[wilkisa]

Thank you so much, VoG. It works perfectly!

 

[wilkisa]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("H7:H14")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Target
    .Offset(, -3).Value = .Offset(, -3).Value - .Value
    .ClearContents
End With
Application.EnableEvents = True
End Sub

then press ALT + Q to close the code window.

Now my user has thrown a curve ball at me (or us!). He has added a second sheet to the workbook and is entering the number in column C (replaces the original column H on Sheet1). He needs the macro to match the name from Sheet2, Column A to the name on Sheet1, Column A and then subtract the value from Sheet2, Column C from Sheet1, Column E. Sheet1, Column E should always be just a number and no formula. We can eliminate Sheet1 columns F, G, & H from the sheet.

Sheet2 will be added onto throughout the month so he only needs it to subtract as he enters each line.

Can anyone help?

 

[VoG]

Try this in Sheet2's code module. I've assumed that the value entered in column C is to be deleted - if not delete the lines in blue

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Found As Range
If Target.Column = 3 Then
    Set Found = Sheets("Sheet1").Columns("A").Find(what:=Target.Offset(, -2).Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not Found Is Nothing Then
        With Found.Offset(, 4)
            .Value = .Value - Target.Value
        End With
        Application.EnableEvents = False
        Target.ClearContents
        Application.EnableEvents = True
    End If
End If
End Sub

 

[wilkisa]

Again, VoG, you are my hero! It works perfectly!!!!

 

[wilkisa]

Okay, now my user wants another change. When he enters the value on Sheet2, he wants to be able to undo it if it is a wrong value. As it is right now, once he hits ENTER he does not have the ability to undo the change. He also is not able to see what the starting value is on Sheet1 unless he looks there first. I am also recommending that he turn on Tracked Changes.

Thanks for any help I can get.
Shirlene

 

[VoG]

That would be difficult in VBA because once a macro runs it deletes the undo stack. There is Application.Undo but that is a bit flaky and has to be pretty much the first line in a procedure.

Say he enters 5 and then changes his mind, all he has to do is enter -5 in the same cell to undo the previous change (I think - please try it)

 

[wilkisa]

Thanks, Peter. It just didn't occur to me that it would work in reverse. Also, the tracked changes doesn't work as I would like so I going to forget that idea. I think the -value will do the job just fine.