I want the cell to change back to 0 after I type in a number and hit enter, I
have a formula in another cell that uses the number I enter, but if the cell
does not return to zero directly after I hit enter, it will keep adding
whatever number is in the cell. Please Help!

This tiny macro does what you want to cell A1, You can adapt it to any cell.

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = 0
Application.EnableEvents = True
End Sub

Remember that this is worksheet code. Right click on the tab, select view
code and paste the stuff in.
--
Gary's Student


"GoodTrouble" wrote:

I want the cell to change back to 0 after I type in a number and hit enter, I
have a formula in another cell that uses the number I enter, but if the cell
does not return to zero directly after I hit enter, it will keep adding
whatever number is in the cell. Please Help!

Ok, I see where to put the code in, but how do I adapt it?? I have many
groups of cells that it needs to work for...
For Example:
C9:C12
C16:C19
H2:H5
H9:H12
H16:H19
Each cell in those ranges has a zero....I need to type a number into it, the
formula from the other cells in the sheet will pull that number, and then I
need this code to erase the number I just typed in and set the cell back to
zero...

"Gary''s Student" wrote:

This tiny macro does what you want to cell A1, You can adapt it to any cell.

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = 0
Application.EnableEvents = True
End Sub

Remember that this is worksheet code. Right click on the tab, select view
code and paste the stuff in.
--
Gary's Student


"GoodTrouble" wrote:

I want the cell to change back to 0 after I type in a number and hit enter, I
have a formula in another cell that uses the number I enter, but if the cell
does not return to zero directly after I hit enter, it will keep adding
whatever number is in the cell. Please Help!

Ok nevermind!! I got it, I just put the cell ranges that I just typed in
there into the code!! Duh! I should have known that! But how can I get it to
work with all the other ranges that I listed, I can only get it to work for
one range...do I just use a comma in between each range??
"Gary''s Student" wrote:

This tiny macro does what you want to cell A1, You can adapt it to any cell.

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = 0
Application.EnableEvents = True
End Sub

Remember that this is worksheet code. Right click on the tab, select view
code and paste the stuff in.
--
Gary's Student


"GoodTrouble" wrote:

I want the cell to change back to 0 after I type in a number and hit enter, I
have a formula in another cell that uses the number I enter, but if the cell
does not return to zero directly after I hit enter, it will keep adding
whatever number is in the cell. Please Help!

This line:
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
becomes:
If Intersect(Range("c9:c12,c16:c19,h2:h5,h9:h12,h16:h 19"), Target) _
Is Nothing Then Exit Sub



GoodTrouble wrote:

Ok, I see where to put the code in, but how do I adapt it?? I have many
groups of cells that it needs to work for...
For Example:
C9:C12
C16:C19
H2:H5
H9:H12
H16:H19
Each cell in those ranges has a zero....I need to type a number into it, the
formula from the other cells in the sheet will pull that number, and then I
need this code to erase the number I just typed in and set the cell back to
zero...

"Gary''s Student" wrote:

This tiny macro does what you want to cell A1, You can adapt it to any cell.

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = 0
Application.EnableEvents = True
End Sub

Remember that this is worksheet code. Right click on the tab, select view
code and paste the stuff in.
--
Gary's Student


"GoodTrouble" wrote:

I want the cell to change back to 0 after I type in a number and hit enter, I
have a formula in another cell that uses the number I enter, but if the cell
does not return to zero directly after I hit enter, it will keep adding
whatever number is in the cell. Please Help!

--

Dave Peterson

Alrighty, working perfectly now! Thank You so much!

"GoodTrouble" wrote:

Ok nevermind!! I got it, I just put the cell ranges that I just typed in
there into the code!! Duh! I should have known that! But how can I get it to
work with all the other ranges that I listed, I can only get it to work for
one range...do I just use a comma in between each range??
"Gary''s Student" wrote:

This tiny macro does what you want to cell A1, You can adapt it to any cell.

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = 0
Application.EnableEvents = True
End Sub

Remember that this is worksheet code. Right click on the tab, select view
code and paste the stuff in.
--
Gary's Student


"GoodTrouble" wrote:

I want the cell to change back to 0 after I type in a number and hit enter, I
have a formula in another cell that uses the number I enter, but if the cell
does not return to zero directly after I hit enter, it will keep adding
whatever number is in the cell. Please Help!