1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: VBA error "400" running an Excel macro.

Discussion in 'Business Applications' started by exerguy, Dec 2, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. exerguy

    exerguy Thread Starter

    Joined:
    Nov 10, 2008
    Messages:
    21
    I have created a macro that goes through my workbook and is supposed to clear the interior color from cells A1:I900 on each page. When I try to run the macro I get a pop up that just says "400" in it with a red circle that has a white "X" through it. Here is the code:

    Sub Clearcolors()
    Dim ws As Worksheet
    Dim RngH As Range
    Dim RngHD As Range
    For Each ws In ThisWorkbook.Worksheets
    ws.Select
    Set RngH = ws.Range("A1:I" & Range("I900").End(xlUp).Row)
    For Each RngHD In RngH
    RngHD.Interior.ColorIndex = xlNone
    Next RngHD
    Next ws
    End Sub

    What can I do to fix this problem? Any suggestions?
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    The first thing you need to do is to add an Error trap to find out what the Error description is, (if it can tell you that is)
    Add this as the first row of code after the "Dim RngHD As Range"
    On Error GoTo Errorcatch

    and at the end of the code put

    exit sub

    Errorcatch:
    MsgBox Err.Description
     
  3. exerguy

    exerguy Thread Starter

    Joined:
    Nov 10, 2008
    Messages:
    21
    Okay. I added the error catch and the error I get is:

    "Method 'Select' of object '_worksheet' failed."

    I'm not too sure where to go from here. Any suggestions?
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,268
    Suggestion 1: check for hidden sheets; you'll have a job trying to select those.

    Suggestion 2: explain what the purpose of the code is, since it doesn't seem to work even with ws.Select suppressed.
     
  5. exerguy

    exerguy Thread Starter

    Joined:
    Nov 10, 2008
    Messages:
    21
    I do have one hidden sheet. Unhiding it allows the macro to work, but is there a way to do this without unhiding it?
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,268
    Not sure why you're specifying row 900. However, can't you just simplify it?

    Sub Clearcolors()
    For Each Sheet In ThisWorkbook.Worksheets
    x = Sheet.Range("I" & Rows.Count).End(xlUp).Row
    Sheet.Range("A1:I" & x).Interior.ColorIndex = xlNone
    Next Sheet
    End Sub
     
  7. exerguy

    exerguy Thread Starter

    Joined:
    Nov 10, 2008
    Messages:
    21
    I was specifying row 900 as that was the last row with data in it. I guess that doesn't need to be done. Simplifying it seems to work faster.


    Thanks. I'll go with this and forget about hiding the one page.
     
  8. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,268
    :confused:

    It ought to work for visible and hidden sheets.
     
  9. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    Just add an IF statement to check the sheets visible property. If the worksheet is hidden you can either skip it completely like I've done in the code below or you can modify it to unhide the sheet first, process it, and then re-hide.

    Code:
    Sub Clearcolors()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
    If ws.Visible = True Then
    ws.Range("A1:I" & Cells(Rows.Count, "I").End(xlUp).Row).Interior.ColorIndex = xlNone
    End If
    Next ws
    End Sub
    Regards,
    Rollin
     
  10. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,268
    As we all know, I know squat about this "Dim" and "Set" stuff.

    Rollin, I know you know loads about it.

    So I see this:

    Set RngH = etc.

    And next there's:

    For Each RngHD in etc.

    But there's no Set for RngHD.

    So ... umm ... that's nonsense, isn't it? :confused:

    EDIT: ah; I see you edited. ;)
     
  11. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    Beautiful! :D
     
  12. exerguy

    exerguy Thread Starter

    Joined:
    Nov 10, 2008
    Messages:
    21
    I'm with Zach on this one Rollin - Beautiful. That works better than the one I had and its simpler. Thanks for all the input Bomb, Rollin and Zach!

    One more quick question: How can I set this up to skip just one sheet?
     
  13. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    Just modify the existing IF statement to evaluate the sheetname as well. In the example below the macro will be executed on all visible sheets in the workbook except for Sheet1

    Code:
    Sub Clearcolors()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
    If ws.Visible = True And ws.Name <> "Sheet1" Then
    ws.Range("A1:I" & Cells(Rows.Count, "I").End(xlUp).Row).Interior.ColorIndex = xlNone
    End If
    Next ws
    End Sub
    Regards,
    Rollin
     
  14. exerguy

    exerguy Thread Starter

    Joined:
    Nov 10, 2008
    Messages:
    21
    Thank you for you help. This is all starting to make a little more sense to me now.
     
  15. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/775340