Announcement

Collapse
No announcement yet.

On Error GoTo code failing on second use

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • On Error GoTo code failing on second use



    Hello, i am working on a project in which i am using the match and index function to copy some information from one excel file to another excel file. When the requested information cannot be found it gives an error and i would like to use the On Error GoTo function to continue with my code. For the first instance this works however for the second instance it does not work for any on error functions at all. It just gives the standard error code which should not be the case because i have the On Error code... I could get it working by making a complete new module for the second part but this is not desirable. Does anyone have an idea what i might be doing wrong? Below the relevant code for this issue:
    (the error occurs at the application.worksheetfunction.match because there is no instance of durchschnittspreis in range "B:B")

    Code:
    Sub Import_files
    
    Code...
    
    On Error GoTo NoDurchschnitts
    a = Application.WorksheetFunction.Match("Durchschnittspreis", Sheets("Angebot Lieferant").Range("B:B"), 0)
    Durchschnittspreis = Application.WorksheetFunction.Index(Range("B:H"), a, 7)
    Part_G = CStr(" Durchschnittspreis  " & Durchschnittspreis & ".")
    Range("X20").Value = Part_A & Part_B & Part_C & Part_D & Part_E & Part_F & Part_G
    return1:
    
    code....
    
    On Error GoTo ErrMsg1
    a = Application.WorksheetFunction.Match(Kwekernaam, Sheets("blad1").Range("B:B"), 0)
    KwekerAdress = Application.WorksheetFunction.Index(Range("B:J"), a, 2) & ", " & Application.WorksheetFunction.Index(Range("B:J"), a, 3)
    KwekerCertificering = Application.WorksheetFunction.Index(Range("B:J"), a, 8)
    
    code...
    
    Exit sub
    NoDurchschnitts:
    Range("X20").Value = Part_A & Part_B & Part_C & Part_D & Part_E & Part_F
    GoTo Return1
    
    Exit sub
    ErrMsg1:
    MsgBox "Kweker " & CStr(Kwekernaam) & " is niet bekend in de database (Adressenlijst)"
    End
    
    End Sub
    Kr,
    Cryptonice


    ******* Got it working by adding "On Error GoTo -1" before the ErrMsg1 ********

    Code:
    On Error GoTo -1
    On Error GoTo ErrMsg1
    a = Application.WorksheetFunction.Match(Kwekernaam, Sheets("blad1").Range("B:B"), 0)
    KwekerAdress = Application.WorksheetFunction.Index(Range("B:J"), a, 2) & ", " & Application.WorksheetFunction.Index(Range("B:J"), a, 3)
    KwekerCertificering = Application.WorksheetFunction.Index(Range("B:J"), a, 8)
    However i read somewhere that you should never realy use the GoTo -1 function, so i was reluctant to use it. Although it works i would still like to know what the issue is and why it is solved by the On Error GoTo -1. So if you have any insights for me, please let me know. Thank you

    Last edited by AlanSidman; April 17th, 2018, 08:48.

  • #2


    Code Tags Added
    Your post does not comply with our Forum RULES. Use code tags around code.

    Posting code between tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window.

    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)
    Alan

    Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
    FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8

    If someone has helped you, say "thank you" by clicking on the Like Button.

    Comment

    Working...
    X