PDA

View Full Version : Clear cell value after data entry

11-29-2007, 07:35 AM
Hi all,

I have a circular reference formula that lets me enter a number into a cell (call it A1) and make the value of another cell increase or decrease by that much (call it A2). I've enabled iterative calculations in order to get this to work. There are a series of cells like this. However, I'd like to get A1 to automatically clear itself after a number has been entered and the value of A2 has already been changed by the formula. This is to prevent recalculation of A2 when other cells with circular references are changed in a similar matter. Is there a way to do this, or perhaps a better way to have the value of A2 change by a specified amount?

Thanks in advance for anyone's help!

figment
11-29-2007, 10:35 AM
try this code

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("A2") = Range("A2") + Range("A1")
Range("A1") = ""
End If
Application.EnableEvents = True
End Sub

11-29-2007, 11:53 AM
Thanks so much for your help, it works perfectly. Just one more question: how can I make that work for two columns that go on indefinitely?

figment
11-29-2007, 12:01 PM
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 Then
Range("C" & Target.Row) = Range("C" & Target.Row) + Target
Target = ""
ElseIf Target.Column = 2 Then
Range("D" & Target.Row) = Range("D" & Target.Row) + Target
Target = ""
End If
Application.EnableEvents = True
End Sub

this will:
take column A and add it to column C
take column B and add it to column D