Zero out the cell macro
 

If you change the setting to Formulas, then you just reverse the problem and
the numbers won't get changed.


Sub Zeros_for_New_Input()
Dim rng As Range, cell As Range
On Error Resume Next
Set rng = Range("Input_area").SpecialCells(xlConstants, _
xlNumbers)
On Error GoTo 0
If Not rng Is Nothing Then
For Each cell In rng
If cell.Locked = False Then
cell.Value = 0
End If
Next
End If
Set rng = Nothing
Set rng = Range("Input_area").SpecialCells(xlFormulas, _
xlNumbers)
On Error GoTo 0
If Not rng Is Nothing Then
For Each cell In rng
If cell.Locked = False Then
cell.Value = 0
End If
Next
End If

End Sub

Regards,
Tom Ogilvy


"MacroMan" wrote in message
...
Your "SpecialCells" setting is set to values, change it to
formulas and you should be okay.

-----Original Message-----
The macro below is one someone in this forum helped me to
start. And it works all the time except for the following
condition. Apparently a cell can be unlocked, but if
there
is a numeric formula such as 1 + 2, then this macro
refuses to override that formula with the value of zero.
However, if I have unlocked the cell, I want the zero
value in there when I run this macro. So, how do I need
to
change this macro to make the work the way I want it?

Thanks,


Bruce



Sub Zeros_for_New_Input()
Dim rng As Range, cell As Range
On Error Resume Next
Set rng = Range("Input_area").SpecialCells(xlConstants,
xlNumbers)
On Error GoTo 0
If Not rng Is Nothing Then
For Each cell In rng
If cell.Locked = False Then
cell.Value = 0
End If
Next
End If
End Sub

.

Zero out the cell macro
 

Works like a charm... Thanks
-----Original Message-----
If you change the setting to Formulas, then you just
reverse the problem and
the numbers won't get changed.


Sub Zeros_for_New_Input()
Dim rng As Range, cell As Range
On Error Resume Next
Set rng = Range("Input_area").SpecialCells(xlConstants, _
xlNumbers)
On Error GoTo 0
If Not rng Is Nothing Then
For Each cell In rng
If cell.Locked = False Then
cell.Value = 0
End If
Next
End If
Set rng = Nothing
Set rng = Range("Input_area").SpecialCells(xlFormulas, _
xlNumbers)
On Error GoTo 0
If Not rng Is Nothing Then
For Each cell In rng
If cell.Locked = False Then
cell.Value = 0
End If
Next
End If

End Sub

Regards,
Tom Ogilvy


"MacroMan" wrote in message
...
Your "SpecialCells" setting is set to values, change it
to
formulas and you should be okay.

-----Original Message-----
The macro below is one someone in this forum helped me
to
start. And it works all the time except for the
following
condition. Apparently a cell can be unlocked, but if
there
is a numeric formula such as 1 + 2, then this macro
refuses to override that formula with the value of
zero.
However, if I have unlocked the cell, I want the zero
value in there when I run this macro. So, how do I need
to
change this macro to make the work the way I want it?

Thanks,


Bruce



Sub Zeros_for_New_Input()
Dim rng As Range, cell As Range
On Error Resume Next
Set rng = Range("Input_area").SpecialCells(xlConstants,
xlNumbers)
On Error GoTo 0
If Not rng Is Nothing Then
For Each cell In rng
If cell.Locked = False Then
cell.Value = 0
End If
Next
End If
End Sub

.



.