View Full Version : [SOLVED:] Strange error message

09-15-2004, 12:07 AM

I have been working on a set of macros for about a week (newbie), and they have suddenly started returning the following error message in an error box:


I have no idea what this means. I initially thought that my spreadsheet was corrupted, but after copying and pasting the modules the error messages continue to pop up. I have also tried reinstalling office. Any ideas?

The offending code is below. It basically clears all sheets in a workbook from sheet2 down, bar a few critical cells in each:

Sub ResetForm()
Dim rBig As Range
Dim rSmall As Range
Dim rSmall1 As Range
Dim rSmall2 As Range
Dim Cell As Range
Dim rNew As Range
' Clear all worksheets for the next set of measurements
Sheet1.Protect UserInterfaceOnly:=True
ans = MsgBox("Do you want to proceed and reset all of the dimensional measurements?", vbOKCancel)
If ans = vbCancel Then Exit Sub
Application.ScreenUpdating = False
Sheet1.Range("C7") = Worksheets.Count - 1
Sheet1.Range("D6") = 1
' Remove the charts
For i = 1 To Sheet1.Range("C7")
Sheets(i + 1).Select
If ActiveSheet.ChartObjects.Count > 0 Then
End If
' Select all but the ranges A1:B3, A5:C5
Set rBig = ActiveSheet.UsedRange
Set rSmall1 = Range("A1:B3")
Set rSmall2 = Range("A5:C5")
Set rSmall = Union(rSmall1, rSmall2)
Set rNew = Nothing
For Each Cell In rBig
If Intersect(Cell, rSmall) Is Nothing Then
If rNew Is Nothing Then
Set rNew = Cell
Set rNew = Union(rNew, Cell)
End If
End If
Next Cell
' Clear the selected ranges
Selection.HorizontalAlignment = xlCenter
Next i
' Default back to Measurement sheet
Application.ScreenUpdating = True
End Sub

Jacob Hilderbrand
09-15-2004, 12:30 AM
One problem may be if rNew is nothing, you cannot select it (although that is not your error, but I received it when I tried your code). You can use an If statement to check:

If Not rNew Is Nothing Then
End If

You have this line in a couple places:


What are your trying to do here?

When you run your code and it generates and error, press the debug button. What line is highlighted?

09-15-2004, 02:37 PM
I am using ActiveSheet.UsedRange to select all used cells in the worksheet. Is this the correct syntax? I cannot use the selectdown or similar methods, as the range of used cells will often not be continuous, and can vary in size quite considerably. Is there any command for just selecting an entire worksheet?

When I run the code I do not get a debug button, but for inserting breakpoints I found that the error is in the "for each cell in rBig" loop.

I'm not sure how the second ActiveSheet.UsedRange got there so I've removed it, but I still get the error message.

Jacob Hilderbrand
09-15-2004, 02:40 PM
To select the range use


To select all cells in a worksheet use


09-15-2004, 02:57 PM
I would change your variable Cell to rCell.


09-15-2004, 10:44 PM
Just figured out a solution, of sorts! Turns out that this code cannot be executed when it is a part of an excel object. As soon as I moved it into a seperate module object, it worked ok.

Jacob Hilderbrand
09-15-2004, 11:36 PM
Glad you got it working. :)

Take Care