Find and find next

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
It would be easier to help if we had some data to work with. Can you post a screen shot of what your data looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html 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. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.
 
Upvote 0
You can use the macro recorder to get the basic code and then adjust it to match your needs.

The final code might look something like this:
Code: 
Sub Find_Loop()

Dim i As Integer
Dim FirstCell As Range
Dim FoundCell As Range
Dim Rng As Range
Dim MyString As String
Dim Message As String


Set Rng = Range("A1:A10")


MyString = Range("B5").Value
Message = MyString & " not found!"


Set FoundCell = Rng.Cells(1, 1)
    
    Set FoundCell = Rng.Find(What:=MyString, _
                            After:=FoundCell, _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False, _
                            SearchFormat:=False)
    
    
    If Not FoundCell Is Nothing Then
    
        'Found something
    
        Do
            i = i + 1
            
            If i = 1 Then
                Set FirstCell = FoundCell
            End If
        
            If Not FoundCell Is Nothing Then
            
                Set FoundCell = Rng.FindNext(After:=FoundCell)
        
            End If
        
            If Not Intersect(FoundCell, FirstCell) Is Nothing Then
                'Loop is starting again
                Message = "Found " & i & " matches!"
                Exit Do
            End If


        
        Loop
    End If


MsgBox Message


End Sub
 
Upvote 0
Hi I have data in sheet1 and sheet2. Sheet 2 ID will match from sheet2. would give you more detail- sheet2 ID & name will search in Sheet1, if ID found then it should match name, if name is different then it will delete the rows from sheet2
[TABLE="width: 532"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD][/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]787a00390[/TD]
[TD]TY UMDERYRGTERA YYC[/TD]
[TD][/TD]
[TD]787a00390[/TD]
[TD]TY UMDERYRGTERA YYC[/TD]
[/TR]
[TR]
[TD]787a00390[/TD]
[TD]TY UMDERYRGTERA, YYC[/TD]
[TD][/TD]
[TD]787a00390[/TD]
[TD]TY UMDERYRGTERA, YYC[/TD]
[/TR]
[TR]
[TD]72a070872[/TD]
[TD]AVGAYY AERVGCEA GMC[/TD]
[TD][/TD]
[TD]72a070872[/TD]
[TD]AVGAYY AERVGCEA GMC[/TD]
[/TR]
[TR]
[TD]72a070872[/TD]
[TD]AVGAYY AERVGCEA GMC[/TD]
[TD][/TD]
[TD]72a070872[/TD]
[TD]AVGAYY AERVGCEA GMC[/TD]
[/TR]
[TR]
[TD]72a25a783[/TD]
[TD]OERGUAOM[/TD]
[TD][/TD]
[TD]72a25a783[/TD]
[TD]AOM EMTERYRGAEA GMC[/TD]
[/TR]
[TR]
[TD]72a25a783[/TD]
[TD]8390-GMDUATRGAY HUB[/TD]
[TD][/TD]
[TD]72a25a783[/TD]
[TD]AOM EMTERYRGAEA GMC[/TD]
[/TR]
[TR]
[TD]72a25a783[/TD]
[TD]-BROKEM ARROY #8895[/TD]
[TD][/TD]
[TD]72a02832a[/TD]
[TD]THERM YOYER COMYAMY[/TD]
[/TR]
[TR]
[TD]72a25a783[/TD]
[TD]ERGUAOM EMTERYRGAEA[/TD]
[TD][/TD]
[TD]72a02832a[/TD]
[TD]therM YoYer ComYaMy[/TD]
[/TR]
[TR]
[TD]72a25a783[/TD]
[TD]AOM EMTERYRGAEA GMC[/TD]
[TD][/TD]
[TD]720772893[/TD]
[TD]RAEY AMERGCAM YATER[/TD]
[/TR]
[TR]
[TD]72a02832a[/TD]
[TD]THERM YOYER COMYAMY[/TD]
[TD][/TD]
[TD]720772893[/TD]
[TD]RAEY AMERGCAM YATER[/TD]
[/TR]
[TR]
[TD]72a02832a[/TD]
[TD]therM YoYer ComYaMy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]720772893[/TD]
[TD]ZOMA AMERGCAM YATER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]720772893[/TD]
[TD]MOGA AMERGCAM YATER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]720772893[/TD]
[TD]A AMERGCAM YATER CO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]720772893[/TD]
[TD]RAEY AMERGCAM YATER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]720772893[/TD]
[TD]RAEY AMERGCAM YATER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">Hi I have data in sheet1 and sheet2. Sheet 2 ID will match from sheet2. would give you more detail- sheet2 ID & name will search in Sheet1, if ID found then it should match name, if name is different then it will delete the rows from sheet2
[TABLE="class: cms_table"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD][/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]787a00390[/TD]
[TD]TY UMDERYRGTERA YYC[/TD]
[TD][/TD]
[TD]787a00390[/TD]
[TD]TY UMDERYRGTERA YYC[/TD]
[/TR]
[TR]
[TD]787a00390[/TD]
[TD]TY UMDERYRGTERA, YYC[/TD]
[TD][/TD]
[TD]787a00390[/TD]
[TD]TY UMDERYRGTERA, YYC[/TD]
[/TR]
[TR]
[TD]72a070872[/TD]
[TD]AVGAYY AERVGCEA GMC[/TD]
[TD][/TD]
[TD]72a070872[/TD]
[TD]AVGAYY AERVGCEA GMC[/TD]
[/TR]
[TR]
[TD]72a070872[/TD]
[TD]AVGAYY AERVGCEA GMC[/TD]
[TD][/TD]
[TD]72a070872[/TD]
[TD]AVGAYY AERVGCEA GMC[/TD]
[/TR]
[TR]
[TD]72a25a783[/TD]
[TD]OERGUAOM[/TD]
[TD][/TD]
[TD]72a25a783[/TD]
[TD]AOM EMTERYRGAEA GMC[/TD]
[/TR]
[TR]
[TD]72a25a783[/TD]
[TD]8390-GMDUATRGAY HUB[/TD]
[TD][/TD]
[TD]72a25a783[/TD]
[TD]AOM EMTERYRGAEA GMC[/TD]
[/TR]
[TR]
[TD]72a25a783[/TD]
[TD]-BROKEM ARROY #8895[/TD]
[TD][/TD]
[TD]72a02832a[/TD]
[TD]THERM YOYER COMYAMY[/TD]
[/TR]
[TR]
[TD]72a25a783[/TD]
[TD]ERGUAOM EMTERYRGAEA[/TD]
[TD][/TD]
[TD]72a02832a[/TD]
[TD]therM YoYer ComYaMy[/TD]
[/TR]
[TR]
[TD]72a25a783[/TD]
[TD]AOM EMTERYRGAEA GMC[/TD]
[TD][/TD]
[TD]720772893[/TD]
[TD]RAEY AMERGCAM YATER[/TD]
[/TR]
[TR]
[TD]72a02832a[/TD]
[TD]THERM YOYER COMYAMY[/TD]
[TD][/TD]
[TD]720772893[/TD]
[TD]RAEY AMERGCAM YATER[/TD]
[/TR]
[TR]
[TD]72a02832a[/TD]
[TD]therM YoYer ComYaMy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]720772893[/TD]
[TD]ZOMA AMERGCAM YATER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]720772893[/TD]
[TD]MOGA AMERGCAM YATER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]720772893[/TD]
[TD]A AMERGCAM YATER CO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]720772893[/TD]
[TD]RAEY AMERGCAM YATER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]720772893[/TD]
[TD]RAEY AMERGCAM YATER[/TD]
[/TR]
</tbody>[/TABLE]

</body>
Hi I have data in sheet1 and sheet2. Sheet 2 ID will match from sheet2. would give you more detail- sheet2 ID & name will search in Sheet1, if ID found then it should match name, if name is different then it will delete the rows from sheet2
[TABLE="class: cms_table"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD][/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]787a00390[/TD]
[TD]TY UMDERYRGTERA YYC[/TD]
[TD][/TD]
[TD]787a00390[/TD]
[TD]TY UMDERYRGTERA YYC[/TD]
[/TR]
[TR]
[TD]787a00390[/TD]
[TD]TY UMDERYRGTERA, YYC[/TD]
[TD][/TD]
[TD]787a00390[/TD]
[TD]TY UMDERYRGTERA, YYC[/TD]
[/TR]
[TR]
[TD]72a070872[/TD]
[TD]AVGAYY AERVGCEA GMC[/TD]
[TD][/TD]
[TD]72a070872[/TD]
[TD]AVGAYY AERVGCEA GMC[/TD]
[/TR]
[TR]
[TD]72a070872[/TD]
[TD]AVGAYY AERVGCEA GMC[/TD]
[TD][/TD]
[TD]72a070872[/TD]
[TD]AVGAYY AERVGCEA GMC[/TD]
[/TR]
[TR]
[TD]72a25a783[/TD]
[TD]OERGUAOM[/TD]
[TD][/TD]
[TD]72a25a783[/TD]
[TD]AOM EMTERYRGAEA GMC[/TD]
[/TR]
[TR]
[TD]72a25a783[/TD]
[TD]8390-GMDUATRGAY HUB[/TD]
[TD][/TD]
[TD]72a25a783[/TD]
[TD]AOM EMTERYRGAEA GMC[/TD]
[/TR]
[TR]
[TD]72a25a783[/TD]
[TD]-BROKEM ARROY #8895[/TD]
[TD][/TD]
[TD]72a02832a[/TD]
[TD]THERM YOYER COMYAMY[/TD]
[/TR]
[TR]
[TD]72a25a783[/TD]
[TD]ERGUAOM EMTERYRGAEA[/TD]
[TD][/TD]
[TD]72a02832a[/TD]
[TD]therM YoYer ComYaMy[/TD]
[/TR]
[TR]
[TD]72a25a783[/TD]
[TD]AOM EMTERYRGAEA GMC[/TD]
[TD][/TD]
[TD]720772893[/TD]
[TD]RAEY AMERGCAM YATER[/TD]
[/TR]
[TR]
[TD]72a02832a[/TD]
[TD]THERM YOYER COMYAMY[/TD]
[TD][/TD]
[TD]720772893[/TD]
[TD]RAEY AMERGCAM YATER[/TD]
[/TR]
[TR]
[TD]72a02832a[/TD]
[TD]therM YoYer ComYaMy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]720772893[/TD]
[TD]ZOMA AMERGCAM YATER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]720772893[/TD]
[TD]MOGA AMERGCAM YATER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]720772893[/TD]
[TD]A AMERGCAM YATER CO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]720772893[/TD]
[TD]RAEY AMERGCAM YATER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]720772893[/TD]
[TD]RAEY AMERGCAM YATER[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
According to your explanation and looking at your data, no rows in Sheet2 would be deleted because every combination of ID plus Name in Sheet2 exists in Sheet1. If this is not correct, please clarify again exactly what should happen using a few examples from your data and referring to specific cells, rows, columns and sheets. Which rows in Sheet2 would you expect to be deleted?
 
Upvote 0
mumps said:
According to your explanation and looking at your data, no rows in Sheet2 would be deleted because every combination of ID plus Name in Sheet2 exists in Sheet1. If this is not correct, please clarify again exactly what should happen using a few examples from your data and referring to specific cells, rows, columns and sheets. Which rows in Sheet2 would you expect to be deleted?
Click to expand...

Hi

720772893 exists 5 times in sheet 1, however name matches only two times. what i need if any of the ID does not match name (from sheet1) which has in sheet2 then it should delete all the rows which has 720772893.
 
Upvote 0
aayaanmayank said:
Hi

720772893 exists 5 times in sheet 1, however name matches only two times. what i need if any of the ID does not match name (in sheet1) from sheet2 then it should delete all the rows which has 720772893.
Click to expand...
just made the slight correction for better understanding
 
Upvote 0
So the following rows would be deleted in Sheet1. Is this correct?

72a25a783 OERGUAOM
72a25a783 8390-GMDUATRGAY HUB
72a25a783 -BROKEM ARROY #8895
72a25a783 ERGUAOM EMTERYRGAEA

720772893 ZOMA AMERGCAM YATER
720772893 MOGA AMERGCAM YATER
720772893 A AMERGCAM YATER CO
 
Upvote 0
mumps said:
So the following rows would be deleted in Sheet1. Is this correct?

72a25a783 OERGUAOM
72a25a783 8390-GMDUATRGAY HUB
72a25a783 -BROKEM ARROY #8895
72a25a783 ERGUAOM EMTERYRGAEA

720772893 ZOMA AMERGCAM YATER
720772893 MOGA AMERGCAM YATER
720772893 A AMERGCAM YATER CO
Click to expand...

No in this situation sheet 1 rows will remain as it is however rows which contains this same ID will delete from sheet 2
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Find Range of Rows to Delete using FIND
Replies
8
Views
119
Skyybot
Skyybot
gtd526
  • Solved
find a date created by a formula
Replies
3
Views
55
MARK858
MARK858
B
  • Solved
how to use find formula in a countif formula
Replies
5
Views
58
etaf
etaf
Dan Wilson
  • Solved
Help with Find & Search
Replies
4
Views
258
Dan Wilson
Dan Wilson
T
  • Question Question
How do I write Code for an API key call using Blank Query on legacy Excel/Mac
Replies
1
Views
57
Ron de Bruin
Ron de Bruin

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

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