Find and find next

[aayaanmayank]

Can someone suggest the code for find and find next

 

[mumps]

I'm still not clear which rows you want to delete in Sheet2. Could you please list the rows to be deleted in Sheet2 in the same way as I have listed them in Post #9 ?

 

[aayaanmayank]

I'm still not clear which rows you want to delete in Sheet2. Could you please list the rows to be deleted in Sheet2 in the same way as I have listed them in Post #9 ?

If i refer this ID 720772893 so it should delete the same from sheet2.

please understand below e.g.

sheet2 has (720772893 - RAEY AMERGCAM YATER), now my code will copy first ID and search this ID in sheet1.
IF sheet1 has this ID then it will count that howmany times this id exists.
Suppose if ID exists 5 times in sheet1 then my code will check and refer (5 times) one by one each ID (720772893 & their name) in sheet1 only and any of the ID has different name e.g. (720772893 - ZOMA AMERGCAM YATER), from sheet2 ID then it will delete the rows which has ID 720772893 in sheet2.

 

[mumps]

In Sheet2 each ID number has the same name. Is there any reason why you have "THERM YOYER COMYAMY" and "therM YoYer ComYaMy" for ID: 72a02832a?

 

[aayaanmayank]

In Sheet2 each ID number has the same name. Is there any reason why you have "THERM YOYER COMYAMY" and "therM YoYer ComYaMy" for ID: 72a02832a?

No reason ...Thats the way Data is..

 

[mumps]

Hopefully, I understood correctly. Try this macro:
Option Compare Text

Sub MatchID()
    Application.ScreenUpdating = False
    Dim LastRow As Long, x As Long
    LastRow = Sheets("Sheet2").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim ID As Range, foundID As Range, sAddr As String
    For x = LastRow To 2 Step -1
        Set foundID = Sheets("Sheet1").Range("A:A").Find(Sheets("Sheet2").Cells(x, 1), LookIn:=xlValues, lookat:=xlWhole)
        If Not foundID Is Nothing Then
            sAddr = foundID.Address
            Do
                MsgBox Sheets("Sheet2").Cells(x, 2)
                If Sheets("Sheet2").Cells(x, 2) <> foundID.Offset(0, 1) Then
                    Sheets("Sheet2").Rows(x).EntireRow.Delete
                    Exit Do
                End If
                Set foundID = Sheets("Sheet1").Range("A:A").FindNext(foundID)
            Loop While foundID.Address <> sAddr
            sAddr = ""
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

 

[aayaanmayank]

Thank you so much! Yes it working absolutely fine.

 

[mumps]

You are very welcome.