Find and find next

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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
IDNameIDName
787a00390TY UMDERYRGTERA YYC787a00390TY UMDERYRGTERA YYC
787a00390TY UMDERYRGTERA, YYC787a00390TY UMDERYRGTERA, YYC
72a070872AVGAYY AERVGCEA GMC72a070872AVGAYY AERVGCEA GMC
72a070872AVGAYY AERVGCEA GMC72a070872AVGAYY AERVGCEA GMC
72a25a783OERGUAOM72a25a783AOM EMTERYRGAEA GMC
72a25a7838390-GMDUATRGAY HUB72a25a783AOM EMTERYRGAEA GMC
72a25a783-BROKEM ARROY #889572a02832aTHERM YOYER COMYAMY
72a25a783ERGUAOM EMTERYRGAEA72a02832atherM YoYer ComYaMy
72a25a783AOM EMTERYRGAEA GMC720772893RAEY AMERGCAM YATER
72a02832aTHERM YOYER COMYAMY720772893RAEY AMERGCAM YATER
72a02832atherM YoYer ComYaMy
720772893ZOMA AMERGCAM YATER
720772893MOGA AMERGCAM YATER
720772893A AMERGCAM YATER CO
720772893RAEY AMERGCAM YATER
720772893RAEY AMERGCAM YATER

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
 
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
IDNameIDName
787a00390TY UMDERYRGTERA YYC787a00390TY UMDERYRGTERA YYC
787a00390TY UMDERYRGTERA, YYC787a00390TY UMDERYRGTERA, YYC
72a070872AVGAYY AERVGCEA GMC72a070872AVGAYY AERVGCEA GMC
72a070872AVGAYY AERVGCEA GMC72a070872AVGAYY AERVGCEA GMC
72a25a783OERGUAOM72a25a783AOM EMTERYRGAEA GMC
72a25a7838390-GMDUATRGAY HUB72a25a783AOM EMTERYRGAEA GMC
72a25a783-BROKEM ARROY #889572a02832aTHERM YOYER COMYAMY
72a25a783ERGUAOM EMTERYRGAEA72a02832atherM YoYer ComYaMy
72a25a783AOM EMTERYRGAEA GMC720772893RAEY AMERGCAM YATER
72a02832aTHERM YOYER COMYAMY720772893RAEY AMERGCAM YATER
72a02832atherM YoYer ComYaMy
720772893ZOMA AMERGCAM YATER
720772893MOGA AMERGCAM YATER
720772893A AMERGCAM YATER CO
720772893RAEY AMERGCAM YATER
720772893RAEY AMERGCAM YATER

<tbody>
</tbody>

</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
IDNameIDName
787a00390TY UMDERYRGTERA YYC787a00390TY UMDERYRGTERA YYC
787a00390TY UMDERYRGTERA, YYC787a00390TY UMDERYRGTERA, YYC
72a070872AVGAYY AERVGCEA GMC72a070872AVGAYY AERVGCEA GMC
72a070872AVGAYY AERVGCEA GMC72a070872AVGAYY AERVGCEA GMC
72a25a783OERGUAOM72a25a783AOM EMTERYRGAEA GMC
72a25a7838390-GMDUATRGAY HUB72a25a783AOM EMTERYRGAEA GMC
72a25a783-BROKEM ARROY #889572a02832aTHERM YOYER COMYAMY
72a25a783ERGUAOM EMTERYRGAEA72a02832atherM YoYer ComYaMy
72a25a783AOM EMTERYRGAEA GMC720772893RAEY AMERGCAM YATER
72a02832aTHERM YOYER COMYAMY720772893RAEY AMERGCAM YATER
72a02832atherM YoYer ComYaMy
720772893ZOMA AMERGCAM YATER
720772893MOGA AMERGCAM YATER
720772893A AMERGCAM YATER CO
720772893RAEY AMERGCAM YATER
720772893RAEY AMERGCAM YATER

<tbody>
</tbody>
 
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

F
  • Solved
Find random names
Replies
4
Views
111
Fire_Chief
F
J
  • Question
VBA code to find replace formula
Replies
7
Views
217
jamesmitchell
J
Ramballah
  • Solved
Finding the first certain value with conditions
2
Replies
18
Views
331
mumps
mumps
D
  • Question
Find matching value in table, check if specific cell in same row is empty, find next if not
Replies
0
Views
229
dubyarly
D
S
  • Question
finding the largest document revision
Replies
5
Views
142
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,455
Messages
6,124,937
Members
449,196
Latest member
Maxkapoor

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