On Error Goto Vba Multiple
Contents |
Forums Excel Questions Multiple On Error Goto Statements in VBA Results 1 to 7 of 7 Multiple On Error Goto Statements in VBAThis is a discussion on Multiple On
On Error Goto Not Working Vba
Error Goto Statements in VBA within the Excel Questions forums, part of the vba error handling best practices Question Forums category; Hi, I am trying to write a macro to open up 4 different workbooks (1 at a on error goto line time), filter the ... 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 Linear Mode Switch to
Vba On Error Goto 0
Hybrid Mode Switch to Threaded Mode Jan 31st, 2008,08:25 PM #1 bellsea New Member Join Date Jan 2008 Posts 3 Multiple On Error Goto Statements in VBA Hi, I am trying to write a macro to open up 4 different workbooks (1 at a time), filter the data based on a given criteria, and then select a range of visible cells only, and then copy
On Error Resume Next
and past them into a master workbook. The issue is with the selection of visible cells. If there is no data in the worksheet then the selection will consist of visible cells only and it generates an error: Run-time error '1004': no cells were found. I used a On Error Goto 10 statement and made it past the first occurence of this error, tried using a On Error Goto 11 on the next file but it still stops. At the risk of looking like a putz with my first attempt at VB, I have attached the code that I wrote for this macro: Sub FILEIMPORT() ' ' FILEIMPORT Macro ' Macro recorded 31/01/2008 by Sean Bell ' Dim pricingdir As String Dim categorydir As String Dim skudir As String Dim reportingdir As String Dim pricingfile As String Dim categoryfile As String Dim skufile As String Dim reportingfile As String Dim demandgroup As String Dim mastername As String Sheets("Update Tab").Activate pricingdir = Worksheets("Update Tab").Range("PRICING_DIR") categorydir = Worksheets("Update Tab").Range("CATEGORY_DIR") skudir = Worksheets("Update Tab").Range("SKU_DIR") reportingdir = Worksheets("Update Tab").Range("REPORTING_DIR") demandgroup = Worksheets("Update Tab").Range("DEMAND_GROUP") pricingfile = Worksheets("Update Tab").Range("PRICING_FILE") categoryfile = Worksheets("Update Tab").Range("CATEGORY_FILE") skufile = Worksheets("Update Tab").Range("SKU_FILE") reportingfile = Worksheets("Update Tab").
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 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 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up On Error Goto: Multiple times up vote 1 down vote favorite 1 I'm using a macro to get infos from a website: Sub get_prices() http://www.mrexcel.com/forum/excel-questions/301105-multiple-error-goto-statements-visual-basic-applications.html Dim IE As Object Dim URL As String Dim cell As Range Dim rng As Range Set rng = Range("A2", Range("A1048576").End(xlUp)) Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True For Each cell In rng.Cells comeco: On Error GoTo pau: URL = Cells(1, 2) & cell.Text & "?utm_source=teste" & Rnd IE.Navigate (URL) Do DoEvents Loop Until IE.READYSTATE = 4 Application.Wait (Now + TimeValue("00:00:05")) Cells(cell.Row, 2) = Mid(IE.Document.GetElementById("ctl00_Conteudo_ctl01_precoPorValue").innertext, 3) Cells(cell.Row, 2).Formula = _ WorksheetFunction.Substitute(WorksheetFunction.Substitute(Cells(cell.Row, 2), ".", ""), ",", http://stackoverflow.com/questions/20957649/on-error-goto-multiple-times ".") Next pau: IE.Quit Set IE = Nothing Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True GoTo comeco: End Sub The problem is that from time to time, my Excel kind of "loses the IE object", and I get the error message "THE OBJECT IS REQUERIED" in the following line: Cells(cell.Row, 2) = Mid(IE.Document.GetElementById("ctl00_Conteudo_ctl01_precoPorValue").innertext, 3) Normally, I just debug the code, and make a new line, like: Set IE = CreateObject("InternetExplorer.Application") and then I resume the macro. Bud I'd like to make something more robust, something that everytime an error occurs, it closes the IE and then reopen it. I'm trying to use on error goto But it only works the first time a error occurs, if it happens again it wont go to the error section. Can anyone help me? vba excel-vba share|improve this question edited Jan 7 '14 at 12:31 asked Jan 6 '14 at 19:25 Fabio Rebelo 54312 Where is the On Error Goto ... statement? If it's only catching the error the first time, it may be that the Resume statement isn't set up correctly. –Blackhawk Jan 6 '14 at 20:01 @Blackhawk, I put comeco: On Error Goto pau: in the beggining, and them at the end of the code I did it: pau: IE.Quit Set IE = CreateObject("InternetExplorer.Application")` goto: comeco –Fabio Rebelo Jan 6 '
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 http://stackoverflow.com/questions/7289220/the-second-of-2-on-error-goto-statements-gets-ignored developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question http://www.actuarialoutpost.com/actuarial_discussion_forum/archive/index.php/t-150653.html x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up The second of 2 'On Error goto ' statements gets ignored up vote 2 down vote favorite I have some code that tries to set 11x17 paper as a default... On Error on error GoTo PageSizeErr ActiveSheet.PageSetup.PaperSize = xlPaperTabloid ' more code here PageSizeErr: On Error GoTo PageErr2 ActiveSheet.PageSetup.PaperSize = xlPaper11x17 'try another 11x17 driver definition GoTo resumePrinting PageErr2: MsgBox ("There's a problem setting Tabloid paper for the printer you have selected." & Chr(10) _ & "If you have an 11x17 printer selected, please contact EMBC, otherwise, try a different printer.") Exit Sub -------------- end of code sample ----------------- When it gets to the second 'ActivateSheet.PageSetup... line, instead of going to PageErr2 lable on error goto I get an error dialog box. (I have a printer selected that doesn't support 11x17 which is what I'm trying to test for.) The multiple error handlers are needed as it seems that different printer drivers handle the setting the differently. Why doesn't the second 'On Error goto ' statement get recognized? excel-vba share|improve this question edited Sep 2 '11 at 21:15 Mike Sherrill 'Cat Recall' 54.2k755101 asked Sep 2 '11 at 21:06 Greg 1112 1 Just change your second "on error goto" to a goto and you're done. –Kevin Sep 2 '11 at 21:27 add a comment| 1 Answer 1 active oldest votes up vote 2 down vote You can't use on error goto within an error handler. See http://www.cpearson.com/excel/errorhandling.htm Maybe try something like this: Sub Tester() Dim pSize As XlPaperSize pSize = xlPaperTabloid On Error GoTo haveError: ActiveSheet.PageSetup.PaperSize = pSize 'print stuff... Exit Sub haveveError: If pSize = xlPaperTabloid Then pSize = xlPaper11x17 Resume End If MsgBox ("Couldn't print using tabloid or 11x17") End Sub share|improve this answer answered Sep 2 '11 at 21:30 Tim Williams 57.7k74263 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email and Password Post as a guest Name Email Post as a guest Name Email discard By posting your answer, you agree to the privacy policy and terms
THAT REFERS TO SLOW-LOADING APPLICATIONS. I'M FAMILIAR WITH "ON ERROR GOTO ..." AND "ON ERROR RESUME NEXT" BUT I WANT TO DO SOMETHING LIKE A MULTIPLE LINE IF STATEMENT: ON ERROR ERRORCACHE = ERRORCACHE + 1 IF ERRORCACHE < 3 THEN GOTO PREVIOUS COMMAND AND RETRY IT ELSE MSGBOX("CRITICAL ERROR PREVENTED PROGRAM FROM CONTINUING TO RUN") END IF END ERROR I KNOW THIS CODE IS WRONG - DO I NEED TO USE AN ERRORHANDLER? I REALLY DON'T, AS DIFFERENT ERRORS ARE ENCOUNTERED AT DIFFERENT POINTS IN THE PROCESS AND I'D LIKE FOR THEM TO BE HANDLED DIFFERENTLY (SAME ERROR CODE, DIFFERENT TREATMENTS). IF ANYONE IS FAMILIAR WITH RUNNING MULTIPLE TASKS WHEN ENCOUNTERING 1 ERROR, ADVICE IS WELCOME. HARRRRRRRRRRRRRRRRRRRRRRRRRR!!! Abused Student10-20-2008, 12:15 PMI am a little confused on what you are trying to do but I am sure I have done what you are trying to do. First off, you can put multiple on error goto statements in. I have done that a couple times. So for the first block of code, if there is an error, go to one error handler. Then in the next block, go to a second error handler. Also, you can set up error handlers with specific err.number so if one part is throwing an error 42 and another is throwing an error 301, you can handle those differently. You can combine each of these and mess up your code sufficiently well to really make things bad but sometimes that is what needs to be done. :shrug: far_side10-20-2008, 12:34 PMSomething like this might be useful, but you'd probably want to duplicate the entire block of code and replace the offending line. Does that help? -far_side maxErrors = 3 errorCount = 0 On Error Resume Next Do While (errorCount <= maxErrors And Not success) x = 1 / 0 'offending line If (Err.Number <> 0) Then errorCount = errorCount + 1 Err.Clear Else success = True End If Loop On Error GoTo 0 'resume normal error processing If (errorCount > maxErrors) Then MsgBox "Fatal error" End If CAPTAIN_MORGAN10-20-2008, 02:19 PMSomething like this might be useful, but you'd probably w