How to use error handling properly inside a for loop

nando88

Board Regular
Joined
Jun 22, 2013
Messages
124
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:
VBA Code:
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.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Perhaps Resume Next - but I can't tell where. Maybe after this line
sheet2.Cells(r1, 1).Interior.Color = vbRed

or after End If
VBA Code:
    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.
VBA Code:
    Next i
    Exit Sub
noencontrado:

Furthermore, anything that is Set in prior lines should be cleared:
VBA Code:
    Next i
    Exit Sub
Set sheet2 = Nothing
Set sheet1 = Nothing
noencontrado:
 
Upvote 0
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:
VBA Code:
            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:
VBA Code:
                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
 
Upvote 0

Forum statistics

Threads
1,223,902
Messages
6,175,278
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top