400 Error Vba Excel
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 excel vba error 1004 further replies. Advertisement exerguy Thread Starter Joined: Nov 10, 2008 Messages: 21 I have created
Error 400 Vba Excel 2010
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 vba error 400 excel 2013 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 error code 400 excel macro 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? exerguy, Dec 2, 2008 #1 Sponsor OBP Trusted Advisor Joined: Mar 8, 2005 Messages: 19,065 The first thing you need to do is to add an Error trap to find out what the Error description
Excel Vba Label Not Defined
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, 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 1
Forums Excel Questions Visual Basic error: 400 Results 1 to 2 of 2 Visual Basic error: 400This is a discussion on Visual Basic error: 400 within the Excel Questions forums, part of the Question Forums category; Hi vba error catch I have a macro which had been working fine, which had the following code: vba error 400 protected sheet With Range("Contact") .Locked = False ... LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread to del.icio.usBookmark in
Microsoft Visual Basic For Applications Error 400 Excel 2013
TechnoratiTweet this thread Thread Tools Show Printable Version Display 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 https://forums.techguy.org/threads/solved-vba-error-400-running-an-excel-macro.775340/ 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 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, http://www.mrexcel.com/forum/excel-questions/481544-visual-basic-error-400-a.html 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,826 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 are unlocking a cell in order to do something with it suggests that there's an issue with worksheet protection. You say the sheet is unprotected - but if so, why must the cell be unlocked? I suspect there are other factors at play - though I can't say what or how, or why your code fix has made a difference. Another try, without selection, might be: Code: Dim r As Range Set r = Range("Contacts") With r '// code End With I'm sur
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or http://stackoverflow.com/questions/11615082/400-error-excel-macro posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only http://p2p.wrox.com/excel-vba/52071-what-error-400-a.html takes a minute: Sign up 400 Error Excel Macro up vote 5 down vote favorite I'm trying to run a macro that will delete rows that don't contain a particular value in column B. Here's my code: Sub deleteRows() Dim vba error count As Integer count = Application.WorksheetFunction.CountA(Range("AF:AF")) Dim i As Integer i = 21 Do While i <= count If (Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("OSR Platform", Range("B" & i))) = False) Then If (Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("IAM", Range("B" & i))) = False) Then Rows(i).EntireRow.Delete i = i - 1 count = count - 1 End If End If i = i + 1 Loop End Sub Now what it SHOULD be doing is the following: 1.) Find the number of rows to go through and set that as count vba error 400 (this works) 2.) Start at row 21 and look for "OSR Platform" and "IAM" in column B [this kind of works (see below)] 3.) If it finds neither, delete the entire row and adjust the count and row number as necessary (this works) For some reason, whenever the code gets to the first If statement, an error window with a red X pops up that just says "400." As far as I can tell, I have written everything syntactically soundly, but clearly there's something wrong. excel vba excel-vba share|improve this question edited Jul 23 '12 at 15:06 asked Jul 23 '12 at 14:56 jrad 2,2731923 No that's not the problem. I've tested this method with other conditions on the If statement and it works just fine. Good thought, though. –jrad Jul 23 '12 at 15:07 @Gaffi - came here to suggest the same thing –LittleBobbyTables Jul 23 '12 at 15:07 Just in case, I just tried what Gaffi had suggested (that I start at the bottom of the table and loop backwards) and I got the same error. –jrad Jul 23 '12 at 15:08 @JackRadcliffe After my first comment, I thought there was more to add, so I've put all of it into an answer below, if it helps. –Gaffi Jul 23 '12 at 15:16 add a comment| 2 Answers 2 active oldest votes up vote 8 down vote accepted You may wa
Unanswered Topics Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA What Is Error 400? User Name Remember Me? Password Reminder Password Register Register | FAQ | Members List | Calendar | Today's Posts | Search Excel VBA Discuss using VBA for Excel programming. Search Forums Show Threads Show Posts Advanced Search Find All Thanked Posts Go to Page... Welcome to the p2p.wrox.com Forums. You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free . Thread Tools Display Modes #1 (permalink) December 29th, 2006, 01:27 PM RollingWoodFarm Authorized User Join Date: Jul 2006 Location: Kalamazoo, MI, USA. Posts: 27 Thanks: 0 Thanked 0 Times in 0 Posts What Is Error 400? I am adding a pop-up calendar to some VB coding. I've already done the add-in through Excel. When I select it in References in VB, I get a Visual Basic error that just says 400 in the message box. There is an image of a red button with an "X" on it. My only selections are "OK" and "HELP". If I select "HELP", I just get a blank page in a new window. Can anyone tell me what this error means? Thanks. Terry #2 (permalink) December 29th, 2006, 11:30 PM UnderTheBridge Registered User Join Date: Dec 2006 Location: , , USA. Posts: 4 Thanks: 0 Thanked 0 Times in 0 Posts From what I have been able to gather, the 400 is a generic illegal operation code that could apply to just about anything. When you say Quote: quote:When I select it in References in VB, I get a Visual Basic error I'm not quite sure what you mean exactly. Do you mean that when you run the code you get the error? What happens when you step through the code? Does that line of code point you to something that you might be overlooking (malformed function, invalid offset, etc)? You might want to paste the code here - would make it easier to visualize. I sympathize on the stupid error message. You would think there would at least be a straightforward listing of what they mean in the VB