Help with an auto clearing script.

Exlnub · Sep 16, 2011

I was hoping someone could help me with a bit of formulating/scripting. What I would like to do is have the input values I place in column B subtract and those I input in column C add to the totals of Column D; however, I would like for the values that I input into B and C to auto clear after they add/subtract to/from column D.
I found the following script online, and while it does the auto clearing and adding/subtracting, it is all done from one column. I have tried to play around with it in the hopes I could figure it out, but…. I am at a loss.

-----------------
Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("B:B")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met
' Add or Subtract Column B's value to/from Column A
If Target.Value <> 0 Then
Target.Offset(, -1).Value = Target.Offset(, -1).Value + Target.Value
Target.Value = ""
End If
End Sub
-------------------

HalfAce · Sep 16, 2011

Hi and welcome to the board.
How about something like this?

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ADDrng As Range, SUBrng As Range

Set SUBrng = Target.Parent.Range("B:B")
Set ADDrng = Target.Parent.Range("C:C")

If Target.Count > 1 Then Exit Sub

If Intersect(Target, ADDrng) Is Nothing And _
   Intersect(Target, SUBrng) Is Nothing Then Exit Sub

If Not Intersect(Target, SUBrng) Is Nothing Then _
  Target(, 3).Value = Target(, 3).Value - Target.Value: Target.ClearContents

If Not Intersect(Target, ADDrng) Is Nothing Then _
  Target(, 2).Value = Target(, 2).Value + Target.Value: Target.ClearContents

End Sub

That get you closer to where you want to be?

Exlnub · Sep 16, 2011

Thanks for the welcome and help, it is very much appreciated. I am getting a debug error with the script you provided, take a look at the SS. What do you think?

Exlnub · Sep 16, 2011

Forget my earlier response; your script is working perfectly now. Obviously i was doing something wrong.

Thanks again, you're a life saver.

HalfAce · Sep 19, 2011

Most welcome. Glad you got it sorted out.

Help with an auto clearing script.

Exlnub

New Member

Excel Facts

HalfAce

MrExcel MVP

Exlnub

New Member

Exlnub

New Member

HalfAce

MrExcel MVP

Similar threads

Forum statistics

Share this page

Help with an auto clearing script.

Exlnub

New Member

Excel Facts

HalfAce

MrExcel MVP

Exlnub

New Member

Exlnub

New Member

HalfAce

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock