Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Multiple on error goto statements

  1. #1
    New Member
    Join Date
    Jan 2015
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Multiple on error goto statements

    Hi all,

    I've found various threads on problems similar to the one I'm having but none of them appear to be working for me.

    All I'm trying to do is copy and paste various results from one excel sheet to another - the macro is really simple and works fine. The only problem is that in some instances some tests have not been performed so an error message will pop up (I'm using the find function and if it's not there I get the error). The obvious solution would be "On Error Goto Err1:" and then have "Err1" where it wants to resume. The problem is this only works once. I've also tried the Err.clear function but that seems to fail too. Here is a section of my code
    Code:
    ''Moisture
        SrcWB.Activate
        On Error GoTo Err1
        Cells.Find(What:="*LNMC_LNMC", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
            
            Selection.Offset(2).Select
        Range(Selection, Selection.End(xlDown)).Copy
        
        ''Paste in
        DestWB.Worksheets("Sheet1").Activate
        Range("n2").Select
        
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
    Err1:
    
    
        ''Moisture & density
        SrcWB.Activate
        On Error GoTo Err2:
        Cells.Find(What:="*LDEN_MC", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
            
            Selection.Offset(2).Select
        Range(Selection, Selection.End(xlDown)).Copy
        
        ''Paste in
        DestWB.Worksheets("Sheet1").Activate
        Range("o2").Select
        
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
    Err2:
        
    ''Bulk Density
        SrcWB.Activate
        On Error GoTo Err3:
        Err.Clear
        Cells.Find(What:="*BDEN_BDEN", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
                    
            Selection.Offset(2).Select
        Range(Selection, Selection.End(xlDown)).Copy
    
    
        ''Paste in
        DestWB.Worksheets("Sheet1").Activate
        Range("P2").Select
        
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
    Err3:
    What am I missing!?

    Thank you for any help.
    Cheers
    Last edited by RoryA; May 12th, 2015 at 11:05 AM.

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,351
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Multiple on error goto statements

    You would need a Resume statement to clear the current exception: On Error WTF? | Excel Matters

    However, you don't really need an error handler here at all - you can simply avoid the error altogether. Instead of this, which will raise an error if the value is not found:
    Code:
    On Error GoTo Err1
    Cells.Find(What:="*LNMC_LNMC", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    Selection.Offset(2).Select
    Range(Selection, Selection.End(xlDown)).Copy
    
    ''Paste in
    DestWB.Worksheets("Sheet1").Activate
    Range("n2").Select
    
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    You should assign the result of the Find to a Range variable and test if that's Nothing:
    Code:
    Dim rngFound as Range
    Set rngFound = Cells.Find(What:="*LNMC_LNMC", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    If not rngFound is Nothing then
    rngFound.Offset(2).Select
    Range(Selection, Selection.End(xlDown)).Copy Destination:=DestWB.Worksheets("Sheet1").Range("n2")
    End If
    and repeat for the other sections.
    Last edited by RoryA; May 12th, 2015 at 11:57 AM. Reason: Correct typo.

  3. #3
    New Member
    Join Date
    Jan 2015
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple on error goto statements

    Cheers man works perfectly!! you just had a little spelling mistake in destination which threw me.

    You've saved me loads of time - nice one!

  4. #4
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,351
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Multiple on error goto statements

    Well spotted - I've corrected the code for posterity.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •