Consulting

Results 1 to 7 of 7

Thread: Solved: Strange error message

  1. #1
    VBAX Regular AJS's Avatar
    Joined
    Sep 2004
    Location
    Melbourne
    Posts
    61
    Location

    Solved: Strange error message

    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:

    [VBA] 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
    [/VBA]

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    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:

    [VBA]If Not rNew Is Nothing Then
    rNew.Select
    End If
    [/VBA]

    You have this line in a couple places:

    [VBA] ActiveSheet.UsedRange[/VBA]

    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?

  3. #3
    VBAX Regular AJS's Avatar
    Joined
    Sep 2004
    Location
    Melbourne
    Posts
    61
    Location
    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.

  4. #4
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    To select the range use

    [VBA] ActiveSheet.UsedRange.Select[/VBA]

    To select all cells in a worksheet use

    [VBA] Cells.Select[/VBA]

  5. #5
    VBAX Regular
    Joined
    Jun 2004
    Posts
    14
    Location
    I would change your variable Cell to rCell.

    TJ
    Oh dear, I need a beer

  6. #6
    VBAX Regular AJS's Avatar
    Joined
    Sep 2004
    Location
    Melbourne
    Posts
    61
    Location
    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.

  7. #7
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Glad you got it working.

    Take Care

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •