adding and subtracting from same cell with leaving the value inserted cell empty for later addition and subtraction

siddheshmumbaikar

New Member
Joined
Jan 5, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
VBA code would be appreciated.
for eg. D1 has value of 100. if i insert 10 in F1 it should get subtracted from D1 leaving F1 empty for later subractions. and if i insert say 20 in cell G1 it should get added in D1 leaving cell G1 empty for later additions.. actually i got the code for subtraction but i am not able to find code for addition. below is the code for subtraction.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F7:F200")) 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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,391
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

When posting code please use code tags. My signature block below has more details.

Give this replacement code a try.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, Cell As Range
  Dim Amt As Double
  
  Set Changed = Intersect(Target, Range("F1:G200"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each Cell In Changed
      If Len(Cell.Value) > 0 Then
        Select Case Cell.Column
          Case 6: Amt = -Cell.Value
          Case 7: Amt = Cell.Value
        End Select
        Range("D" & Cell.Row).Value = Range("D" & Cell.Row).Value + Amt
        Cell.ClearContents
      End If
    Next Cell
    Application.EnableEvents = True
  End If
End Sub
 
Solution

siddheshmumbaikar

New Member
Joined
Jan 5, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Thanks a lot, This has made my day. appreciate your help very much, thank you once again.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,237
Messages
5,623,542
Members
415,977
Latest member
Tommyboy30

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top