Excel 2007 Vba Error 400
Contents |
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 exerguy Thread error 400 vba excel 2010 Starter Joined: Nov 10, 2008 Messages: 21 I have created a macro that goes through my workbook
Excel Vba Error Code 400
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 vba error 400 excel 2013 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
Error Code 400 Excel Macro
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? exerguy, Dec 2, 2008 #1 Sponsor OBP Trusted Advisor Joined: Mar 8, 2005 Messages: 19,075 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 excel vba label not defined 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, Dec 2, 2008 #2 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? exerguy, Dec 2, 2008 #3 bomb #21 Joined: Jul 1, 2005 Messages: 8,284 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. bomb #21, Dec 3, 2008 #4 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? exerguy, Dec 3, 2008 #5 bomb #21 Joined: Jul 1, 2005 Messages: 8,284 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 bomb #21, Dec 3, 2008 #6 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. Tha
Forums Excel Questions Visual Basic error: 400 Results 1 to 2 of 2 Visual Basic error: 400This is a discussion on Visual Basic
Vba Error Catch
error: 400 within the Excel Questions forums, part of the Question Forums
Vba Error 400 Protected Sheet
category; Hi I have a macro which had been working fine, which had the following code: With Range("Contact") microsoft visual basic 400 mac .Locked = False ... LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread Thread Tools Show Printable Version Display https://forums.techguy.org/threads/solved-vba-error-400-running-an-excel-macro.775340/ Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Jul 15th, 2010,05:32 PM #1 peejay Board Regular Join Date Jul 2003 Location Sydney Posts 53 Visual Basic error: 400 Hi I have a macro which had been working fine, which had the following code: With Range("Contact") .Locked = False .Copy .PasteSpecial Paste:=xlPasteValues .Interior.ColorIndex = 36 End With where http://www.mrexcel.com/forum/excel-questions/481544-visual-basic-error-400-a.html the range "Contact" is on the active sheet, and the active sheet is unprotected. For some reason, the macro started throwing up an error box, titled 'Microsoft Visual Basic', showing only '400' (with OK & Help buttons). I've fixed the problem, changing the 1st two lines to the following: Range("Contact").Select With Selection but, I'd be interested in understanding why this has occurred. Thanks, PJ Share Share this post on Digg Del.icio.us Technorati Twitter cheers, PJ Reply With Quote Jul 16th, 2010,02:11 PM #2 xenou MrExcel MVPModerator Join Date Mar 2007 Location Clev. OH, USA Posts 14,852 Re: Visual Basic error: 400 400 errors are, by definition, unspecified and (for all intents and purposes) mysterious. Your code should not fail here, as far as I can tell. You may even find that changing it back, it will now work again. The only way I know of to troubleshoot 400 errors is to step through the code line by line - and watch for the moment when it crashes. From on outside perspective, the fact that you ar
The How-To Geek Forums Have Migrated to Discourse How-To Geek Forums / Microsoft Office Error 400 in MS Excel 2007 (15 posts) Started 8 years ago by karteekkarteek001 Latest reply http://www.howtogeek.com/forum/topic/error-400-in-ms-excel-2007 from Lighthouse Topic Viewed 13230 times karteekkarteek001 Posts: 57 This post has been reported. I have installed an Add-In from www.download.com for MS Excel with the name 'Simple Excel Shortcuts'. The installation file has an .xla file as add-in. I installed the same. But when I open the MS Excel and clicking on the 'Data' tab immediately an error message with 'Microsoft Visual Basic' with a white 'X' symbol inside a vba error red circle showing error number 400 with two options below 'OK' and 'Cancel'. If I click on 'OK' the dialog box is being closed and no problem further. If I close Excel and re-open it again same thing is occurring. I tried for the system restore option, but it was not having any restore point before this add-on was installed. I tried to repair Office but it came that it can't be vba error 400 repaired. After that I tried to uninstall the whole Microsoft Office 2007 and reinstall it. But after so much time of displaying that boring MS logo, it was giving a dialog box with 'Microsoft Office Enterprise 2007 could not be unistalled due to some system errors. Please try again later.' How can I get rid of this annoying 400 error. Please help me friends... :-( P.S. I got a great lesson that never try any Add-on if you are not sure about how is it going to mess you up or how can you get rid of that mess if it messes you! Reports: · Posted 8 years ago Top karteekkarteek001 Posts: 57 This post has been reported. To make the discussion specific to the point, I would like to give the exact phrases that were appearing on the uninstllation screen. They were 'Microsoft Office Enterprise 2007 did not uninstall successfully'. Only this one line was being displayed with a 'OK' button. K Reports: · Posted 8 years ago Top whs Posts: 17584 This post has been reported. This is strange. There should be a restore point from just before you downloaded the Add-in (or was that a long time ago?). Do the following: Go to All Programs > Acces