Line item calculation

[Carl Johnson]

I would like to enter a line item expenditure(E6) and have the yearly total
expenditures updated to reflect the change(I6) then the line item
expenditure cell cleared until repeated. Can I do this and if so how? Your
help would surely be appreciated.

 

[Tom Ogilvy]

Right click on the sheet tab where you want this behavior. Paste in code
like this:

Private Sub Worksheet_Change(ByVal _
Target As Excel.Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$E$6" Then
If Not IsEmpty(Target) Then
If IsNumeric(Target) Then
Range("I6").Value = _
Range("I6").Value + Target.Value
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End If
End If
End Sub

 

[Carl Johnson]

Thanks Tom worked great.

Right click on the sheet tab where you want this behavior. Paste in code
like this:

Private Sub Worksheet_Change(ByVal _
Target As Excel.Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$E$6" Then
If Not IsEmpty(Target) Then
If IsNumeric(Target) Then
Range("I6").Value = _
Range("I6").Value + Target.Value
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End If
End If
End Sub

 

[Carl Johnson]

Tom, one last question. Can I alter the code in a way that will allow
the code to work in multible cells without having to write the code for
each cell.

Thank you again
Carl

 

[Tom Ogilvy]

Sure, but you haven't said what the multiple cells are.

For column E, rows 2 to 10

Private Sub Worksheet_Change(ByVal _
Target As Excel.Range)
Dim rng as Range
If Target.Count > 1 Then Exit Sub
If Target.row => 2 and Target.Row <=10 then
If Target.column = 5 Then
If Not IsEmpty(Target) Then
If IsNumeric(Target) Then
Application.EnableEvents = False
set rng = Target.Offset(0,4)
rng.Value = rng.value + Target.Value
Target.ClearContents
Application.EnableEvents = True
End If
End If
End If
End If
End Sub