Strange error message

[AJS]

Hi,

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:

400

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
ActiveSheet.ChartObjects.Delete
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
Else
Set rNew = Union(rNew, Cell)
End If
End If
Next Cell
rNew.Select
' Clear the selected ranges
Selection.ClearContents
Selection.ClearFormats
Selection.HorizontalAlignment = xlCenter
Range("A6").Select
ActiveSheet.UsedRange
Next i
' Default back to Measurement sheet
Sheet1.Select
Range("D6").Select
ActiveSheet.UsedRange
Application.ScreenUpdating = True
End Sub

[Jacob Hilderbrand]

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
            rNew.Select
        End If

You have this line in a couple places:

ActiveSheet.UsedRange

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?

[AJS]

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]

To select the range use

ActiveSheet.UsedRange.Select

To select all cells in a worksheet use

Cells.Select

[tinyjack]

I would change your variable Cell to rCell.

TJ

[AJS]

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]

Glad you got it working.

Take Care