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:
Any help will be greatly appreciated.
Thanks in advance.
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.