Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

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


(!)

exerguy's Avatar
exerguy exerguy is offline
Junior Member with 21 posts.
THREAD STARTER
 
Join Date: Nov 2008
Experience: constantly learning
02-Dec-2008, 04:47 PM #1
Solved: VBA error "400" running an Excel macro.
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,617 posts.
 
Join Date: Mar 2005
Location: UK
02-Dec-2008, 05:54 PM #2
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
__________________
OBP
I do not give up easily
exerguy's Avatar
exerguy exerguy is offline
Junior Member with 21 posts.
THREAD STARTER
 
Join Date: Nov 2008
Experience: constantly learning
02-Dec-2008, 11:04 PM #3
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?
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
03-Dec-2008, 04:31 AM #4
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.

Last edited by bomb #21; 03-Dec-2008 at 05:42 AM..
exerguy's Avatar
exerguy exerguy is offline
Junior Member with 21 posts.
THREAD STARTER
 
Join Date: Nov 2008
Experience: constantly learning
03-Dec-2008, 10:25 AM #5
I do have one hidden sheet. Unhiding it allows the macro to work, but is there a way to do this without unhiding it?
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
03-Dec-2008, 10:36 AM #6
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's Avatar
exerguy exerguy is offline
Junior Member with 21 posts.
THREAD STARTER
 
Join Date: Nov 2008
Experience: constantly learning
03-Dec-2008, 01:57 PM #7
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.
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
03-Dec-2008, 04:32 PM #8


It ought to work for visible and hidden sheets.
Rollin_Again's Avatar
Member with 4,698 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
03-Dec-2008, 06:19 PM #9
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

Last edited by Rollin_Again; 03-Dec-2008 at 06:31 PM..
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
03-Dec-2008, 06:27 PM #10
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?

EDIT: ah; I see you edited.
Zack Barresse's Avatar
Computer Specs
Member with 5,434 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
03-Dec-2008, 11:31 PM #11
Quote:
Originally Posted by Rollin_Again View Post
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!
exerguy's Avatar
exerguy exerguy is offline
Junior Member with 21 posts.
THREAD STARTER
 
Join Date: Nov 2008
Experience: constantly learning
04-Dec-2008, 02:24 PM #12
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?
Rollin_Again's Avatar
Member with 4,698 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
04-Dec-2008, 05:29 PM #13
Quote:
Originally Posted by exerguy View Post
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's Avatar
exerguy exerguy is offline
Junior Member with 21 posts.
THREAD STARTER
 
Join Date: Nov 2008
Experience: constantly learning
04-Dec-2008, 05:51 PM #14
Thank you for you help. This is all starting to make a little more sense to me now.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑