Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Line item calculation
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Line item calculation
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 -- Regards, Tom Ogilvy "Carl Johnson" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Line item calculation
Thanks Tom worked great.
"Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Carl Johnson" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Line item calculation
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Line item calculation
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 -- Regards, Tom Ogilvy "Carl Johnson" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Line Item | Excel Worksheet Functions | |||
Break one line item into many... | Excel Worksheet Functions | |||
Automating a line item for several worksheets? | Excel Worksheet Functions | |||
How do you create an Excel Gantt chart with double bar per line item ? | Excel Worksheet Functions | |||
deleting line item in import function causes REF# error | Excel Worksheet Functions |