How to use error handling properly inside a for loop

[nando88]

I am trying to hgilight all the cells in a specific column in which a match isn't found.
I came up with some code that works perfectly as is, but the problem is that it exits the execution after encountering the first error, and I would like to continue the execution until the for loop ends and also highlight in red all the cells that contain the search value that isn't found.

This is the working version that only finds the first match:

Sub Mover()
'
' Mover Macro
'

'
Dim sheet1 As Worksheet, sheet2 As Worksheet
Set sheet2 = Sheets("Trabajo")
Set sheet1 = Sheets("Referencia")
Dim er As String
Dim r As Long, r1 As Long
Dim LR As Long, i As Long, line As Long, line2 As Long, count As Long
    On Error GoTo noencontrado
    LR = sheet1.Cells(Rows.count, 1).End(xlUp).Row * 2
    sheet1.Select
    Range(Cells(2, 1).Address, Cells(LR, 8).Address).Select
    Selection.Copy
    sheet2.Select
    sheet2.Cells(2, 1).Select
    ActiveSheet.Paste
    sheet2.Paste
    count = 0

    For i = 2 To LR
    'For j = 2 To LR
        If sheet2.Cells(i, 6) = 0 Then
            sheet2.Range(Cells(i + 1, 1).Address, Cells(LR, 8).Address).Select
            Selection.Cut
            sheet2.Range(Cells(i + 2, 1).Address).Select
            ActiveSheet.Paste
            If Not IsEmpty(Cells(i + 2, 1)) Then
            'Error
            r = i + 2
            er = "El punto medio " & Cells((i + 2), 1) & " no se ha encontrado en la columna de punto Final!"
            line = Application.Match(Cells(i + 2, 1), Range("b:b"), 0)
            Cells(i + 1, 4) = Cells(line, 4)
            Cells(i + 1, 5) = Cells(line, 5)
            Cells(i + 1, 6) = Cells(line, 6)
            End If
            'count = count + 1
        End If
        'If i = LR + count Then
            'Cells(i, 6) = 1
            'Debug.Print "" & LR + count
            'Cells(LR + count, 6) = 0
        'End If
    'Next j
    If i = (LR / 2) Then
        Exit Sub
    End If
    Next i
noencontrado:
    r1 = r
    Err.Clear
    sheet2.Cells(r1, 1).Interior.Color = vbRed
    If r1 = LR Then
        MsgBox er, vbSystemModal + vbExclamation, "Error en la busqueda de coordenadas"
    End If
End Sub

Any help will be greatly appreciated.
Thanks in advance.

 

[Micron]

Perhaps Resume Next - but I can't tell where. Maybe after this line
sheet2.Cells(r1, 1).Interior.Color = vbRed

or after End If

    If r1 = LR Then
        MsgBox er, vbSystemModal + vbExclamation, "Error en la busqueda de coordenadas"
    End If

You should have an exit point so that you don't end up in an error handle when code is finished doing what it's supposed to do.

    Next i
    Exit Sub
noencontrado:

Furthermore, anything that is Set in prior lines should be cleared:

    Next i
    Exit Sub
Set sheet2 = Nothing
Set sheet1 = Nothing
noencontrado:

 

[Alex Blakenburg]

I am having similar issues to @Micron You really want to avoid having to work with Select and ActiveSheet, it makes it difficult to follow what sheet, range or cell you are working with.
As I see if the only place it is likely to be causing you an error is the Match function and related lines. I wouldn't handle that with a VBA error.
Make the below replacement and put the Exit Sub per Micron's suggestion before the Error Label, but also check if the Error section still serves any purpose.

So little on what you are trying to achieve, start with replacing:

            line = Application.Match(Cells(i + 2, 1), Range("b:b"), 0)
            Cells(i + 1, 4) = Cells(line, 4)
            Cells(i + 1, 5) = Cells(line, 5)
            Cells(i + 1, 6) = Cells(line, 6)

with this:

                With Application
                    line = .IfError(.Match(Cells(i + 2, 1), Range("b:b"), 0), 0)
                End With
                If line = 0 Then
                        Sheet2.Cells(r, 1).Interior.Color = vbRed
                Else
                        Cells(i + 1, 4) = Cells(line, 4)
                        Cells(i + 1, 5) = Cells(line, 5)
                        Cells(i + 1, 6) = Cells(line, 6)
                End If