error on exit for VBA

[negi]

Hi experts,
i m using this code but its not working when found error or no value. please help on this

i want if any cell found value then skip for next cell


Sub match()
Dim rng As Range, FinalResult As Variant, Table_Range As Range
Dim L1 As Range
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim cn_m As Range
Dim lastrow As Long
Dim i As Long


Set ws = Sheets("Data")
Set ws1 = Sheets("Sheet2")
Set cn_m = ws.Range("A:A")

lastrow = ws1.Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To lastrow

Set L1 = ws1.Range("A" & i)

On Error GoTo NM

If Application.WorksheetFunction.match(L1, cn_m, 0) > 0 Then

Exit For

Else
NM:

MsgBox "New Record"

End If
Next i
End Sub

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Alex Blakenburg]

i want if any cell found value then skip for next cell

The Exit For you have in your code contradicts the statement above since it is applying the Exit For when the match > 0, which equals Found.

See if the below points you in the right direction. You will need to determine what action to take when found vs not found.

Sub match_mod()
    Dim rng As Range, FinalResult As Variant, Table_Range As Range
    Dim L1 As Range
    Dim ws As Worksheet
    Dim ws1 As Worksheet
    Dim cn_m As Range
    Dim lastrow As Long
    Dim i As Long
    
    Set ws = Sheets("Data")
    Set ws1 = Sheets("Sheet2")
    Set cn_m = ws.Range("A:A")
    
    lastrow = ws1.Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To lastrow
    
        Set L1 = ws1.Range("A" & i)
        
        If Application.IfError(Application.match(L1, cn_m, 0), 0) > 0 Then
            MsgBox "Found: " & L1.Value
        Else
            MsgBox "New Record"
        End If
    Next i
End Sub