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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
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
 

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?
 
Joined
Jul 1, 2005
Messages
8,546
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.
 

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?
 
Joined
Jul 1, 2005
Messages
8,546
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
 

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.
 
Joined
Sep 4, 2003
Messages
4,912
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
 
Joined
Jul 1, 2005
Messages
8,546
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. ;)
 
Joined
Jul 25, 2004
Messages
5,458
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
Beautiful! :D
 

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?
 
Joined
Sep 4, 2003
Messages
4,912
One more quick question: How can I set this up to skip just one sheet?
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
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 2)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top