Error Handling in a Loop

[RLJ]

Hello,

I have a workbook that is a main tracking workbook that needs to look up data from a user selected workbook. What I need it to do is to loop through column B in the Main tracking workbook, look up the value from column b in the user selected workbook and copy/paste data over to the main tracking workbook. This works fine if it finds all the values in the user selected workbook, however the values from the main tracing workbook come from multiple user selected workbooks so there will be errors. I need help in looping through the errors. When it gets to the second error the code stops. I need to have each error cleared as it runs through the looping code.

Sub FindKey()


    '/////////////////////////////////////////////////////////////////////////
    
    
    '/////////////////////////////////////////////////////////////////////////
    
    Application.ScreenUpdating = False
    Dim lookVal As String
NextProp:
    Windows(mdt).Activate
    
    Sheets("Deal Tracker").Select
    ActiveCell.Offset(1, 0).Select 'Looks up Porperty Name
    
    Do Until IsEmpty(ActiveCell.Value)
        
    lookVal = ActiveCell.Value 'Property Name
     
    Windows(ws).Activate
    Sheets("Tracker").Select 'Main Sheet that is being Updated
    
    On Error GoTo NextProp
    Cells.Find(What:=lookVal, After:=ActiveCell, LookIn:= _
        xlFormulas, lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
        Range("C" & (ActiveCell.Row)).Select
        
    ActiveCell.Offset(0, 9).Copy
    
    Windows(mdt).Activate
    
    Sheets("Deal Tracker").Select 'Main Sheet where data is being copied
    ActiveCell.Offset(0, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
        :=False, Transpose:=False
        
    Windows(ws).Activate
    Sheets("Tracker").Select 'Main Sheet that is being Updated
    ActiveCell.Offset(0, 10).Copy


    Windows(mdt).Activate
    
    Sheets("Deal Tracker").Select 'Main Sheet where data is being copied
    ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
        :=False, Transpose:=False
        
    Windows(ws).Activate
    Sheets("Tracker").Select 'Main Sheet that is being Updated
    ActiveCell.Offset(0, 19).Copy


    Windows(mdt).Activate
    
    Sheets("Deal Tracker").Select 'Main Sheet where data is being copied
    ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
        :=False, Transpose:=False
        
    Windows(ws).Activate
    Sheets("Tracker").Select 'Main Sheet that is being Updated
    ActiveCell.Offset(0, 20).Copy


    Windows(mdt).Activate
    
    Sheets("Deal Tracker").Select 'Main Sheet where data is being copied
    ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
        :=False, Transpose:=False


    ActiveCell.Offset(1, -5).Select
    Loop
    
    Application.ScreenUpdating = True
    
End Sub

Thanks for your help!

 

[tonyyy]

RLJ,

You might consider moving the NextProp: label inside the Loop and adding an If Err.Number statement...

Sub FindKey()
    Application.ScreenUpdating = False
    Dim lookVal As String

    Windows(mdt).Activate
    Sheets("Deal Tracker").Select
    ActiveCell.Offset(1, 0).Select 'Looks up Porperty Name
    
    On Error GoTo NextProp
    Do Until IsEmpty(ActiveCell.Value)
        lookVal = ActiveCell.Value 'Property Name
        Windows(ws).Activate
        Sheets("Tracker").Select 'Main Sheet that is being Updated
        Cells.Find(What:=lookVal, After:=ActiveCell, LookIn:= _
            xlFormulas, lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
            xlNext, MatchCase:=False, SearchFormat:=False).Activate
        Range("C" & (ActiveCell.Row)).Select
        ActiveCell.Offset(0, 9).Copy
        
        Windows(mdt).Activate
        Sheets("Deal Tracker").Select 'Main Sheet where data is being copied
        ActiveCell.Offset(0, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
            :=False, Transpose:=False
            
        Windows(ws).Activate
        Sheets("Tracker").Select 'Main Sheet that is being Updated
        ActiveCell.Offset(0, 10).Copy
    
        Windows(mdt).Activate
        Sheets("Deal Tracker").Select 'Main Sheet where data is being copied
        ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
            :=False, Transpose:=False
            
        Windows(ws).Activate
        Sheets("Tracker").Select 'Main Sheet that is being Updated
        ActiveCell.Offset(0, 19).Copy
    
        Windows(mdt).Activate
        Sheets("Deal Tracker").Select 'Main Sheet where data is being copied
        ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
            :=False, Transpose:=False
            
        Windows(ws).Activate
        Sheets("Tracker").Select 'Main Sheet that is being Updated
        ActiveCell.Offset(0, 20).Copy
    
        Windows(mdt).Activate
        Sheets("Deal Tracker").Select 'Main Sheet where data is being copied
        ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
            :=False, Transpose:=False
        ActiveCell.Offset(1, -5).Select
        
[COLOR=#ff0000]NextProp:
        If Err.Number <> 0 Then
            Windows(mdt).Activate
            Sheets("Deal Tracker").Select
            ActiveCell.Offset(1, 0).Select 'Looks up Porperty Name
        End If[/COLOR]
    Loop
    Application.ScreenUpdating = True
End Sub

Cheers,

tonyyy

 

[Fluff]

Also have a look at this http://excelmatters.com/2015/03/17/on-error-wtf/
For error handling.