Object range failed

[revinfo]

Ok, the first line of set rung = Range works, but the second time it bombs, why?
This has been working for several years. The first pass, it checks the constants for a protected status, the check is for formulas. If I comment out the bottom half, the thing isn't working now anyway. I can't imagine what is different than in the past.

All this does is put a 0 value in unprotected cells in a range.


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)

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

 

[GS]

Ok, the first line of set rung = Range works, but the second time it bombs,
why? This has been working for several years. The first pass, it checks the
constants for a protected status, the check is for formulas. If I comment
out the bottom half, the thing isn't working now anyway. I can't imagine
what is different than in the past.

All this does is put a 0 value in unprotected cells in a range.


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)

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

The only thing I can think of is that there are no cells containing
formulas, OR it's because "Areas" are being returned.


<FWIW>
You should move this line...

Set rng = Nothing

...to the last line before "End Sub". It's not necessary to destroy the
object before resetting its ref, but you should destroy it when you'r
done using it as a matter of "good programming practice"!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion