Try:
Sub RaydenUKzz()
Dim i As Long, x As Long
x = 0
For i = 2 To Range("A" & Rows.Count).End(3).Row
If Len(Cells(i, "A")) = 9 Then x = x + 1
Next i
If x > Range("G" & Rows.Count).End(3).Row - 1 Then
MsgBox "Error! Patient entries do not match"
Exit Sub
Else
With Range("B2:B" & Range("A" & Rows.Count).End(3).Row)
.Formula = "=VLOOKUP(A2,$G$2:$I$13,2,FALSE)"
.Offset(, 1).Formula = "=VLOOKUP(A2,$G$2:$I$13,3,FALSE)"
End With
With Range("B2:C" & Range("A" & Rows.Count).End(3).Row)
.Value = .Value
.Replace "#N/A", "", xlWhole
End With
MsgBox "Success! All entries match"
End If
End Sub
Bookmarks