Decrement from a total

[Brad L]

Hi,
I'm using the following code to decrement expenses (column C) from a total (A1).

This works great with one small problem. If I change any value in column C, say from $100 to $50, it deducts both values from A1 instead of updating the value.

Example:
A1 is $500

C5 is changed from $100 to $50. A1 should read $450, instead it's deducting both and reading $350 even though I replaced the original value.

thx

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("C3:C20")) Is Nothing Then
Application.EnableEvents = False
If Target.Value = "" Then
Application.Undo
Range("A1").Value = Range("A1").Value + Target.Value
Target.ClearContents
Else
Range("A1").Value = Range("A1").Value - Target.Value
End If
Application.EnableEvents = True
End If
End Sub

 

[dfenton21]

Hi there,

You are not getting the desired result because your code never stores 500.

Based on your example above, the following is happening:

Range("A1").Value = Range("A1").Value - Target.Value
A1 = 500 - 100
A1 now = 400

Change 100 to 50

Range("A1").Value = Range("A1").Value - Target.Value
A1 = 400 - 50
A1 now = 350 because it's value started at 400 for this calculation

Personally, I'd just use a formula for this calculation, i.e.

=500-sum(C3:C20)

Otherwise, you are trying to do with your VBA code what Excel (and spreadsheets in general) are designed to do with very basic functionality.

If you need to use VBA, I'd still use a formula as first preference, i.e.

<code>
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Range("A1").Value = 500 - Application.WorksheetFunction.Sum(Range("C3:C20"))

End Sub
</code>

You can always input the 500 figure into your formula programatically, using a named cell or an input box (for example).

Hope this helps.
Damien