Help with .Find and .FindNext in VBA

csinger22

New Member
Joined
Nov 16, 2015
Messages
2
Hello,

I have an excel spreadsheet with duplicate/similar names in it (in the thousands). I need to be able to find the duplicates/similar names by taking a substring from the original string value starting in cell (1,1) and comparing it to all other string values in the worksheet. This means that I am not searching just for one string. I am searching first to see if there are duplicates for the string located in cell (1,1), then searching for duplicates to cell (2,2) and so on through the entire worksheet. Right now my code only words for finding all the dupes for a specific string defined by me. How do I iterate through the entire spreadsheet (changing the value I am searching on each time?)

Code I have already.

Sub FindAndExecute()


Dim i As Integer
Dim LastRow As Long
Dim OriginalRange, SearchRange As Range
Dim Sh As Worksheet
Dim Loc As Range
Dim Search, SubSearch As String
LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
OriginalRange = ActiveSheet.UsedRange
SubSearch = Cells(11, 3).Value






For Each Sh In ThisWorkbook.Worksheets
With Sh.UsedRange
Set Loc = .Cells.Find(What:=Mid(SubSearch, 1, 8), SearchDirection:=xlNext)
If Not Loc Is Nothing Then
Do Until Loc Is Nothing
Loc.Value = "Answered!"
Set Loc = .FindNext(Loc)
Loop
End If
End With
Set Loc = Nothing
Next




End Sub
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
[/QUOTE]
Hello,


I have an excel spreadsheet with duplicate/similar names in it (in the thousands).
What do you mean by "names"? Cell values?
I need to be able to find the duplicates/similar names by taking a substring from the original string value starting in cell (1,1) and comparing it to all other string values in the worksheet. This means that I am not searching just for one string. I am searching first to see if there are duplicates for the string located in cell (1,1), then searching for duplicates to cell (2,2) and so on through the entire worksheet.
Are you searching diagonally? Or for all cells?
Right now my code only words for finding all the dupes for a specific string defined by me.
Your current value is stored in C11 of the active sheet?
How do I iterate through the entire spreadsheet (changing the value I am searching on each time?)
You would have to loop through each cell. This may work decently if you have a few thousand cells. You are going to run into performance/efficiency problems soon. There are much better algorithms than the quadratic one you outlined.
Code I have already.


This would be an example of how you handle this type of situation.


Code:
Sub FindAndExecute()


  Dim i As Integer
  Dim LastRow As Long
  Dim OriginalRange, SearchRange As Range
  Dim Sh As Worksheet
  Dim Loc As Range
  Dim Search, SubSearch As String
  
  Dim i as Long, j as Long
  
  LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
  OriginalRange = ActiveSheet.UsedRange
  
  For i = 1 to OriginalRange.Rows.Count
    For j = 1 to OriginalRange.Columns.Count
      SubSearch = Cells(i, j).Value


      For Each Sh In ThisWorkbook.Worksheets
        With Sh.UsedRange
          Set Loc = .Cells.Find(What:=Mid(SubSearch, 1, 8), SearchDirection:=xlNext)
          Do Until Loc Is Nothing
            Loc.Value = "Answered!"
            Set Loc = .FindNext(Loc)
          Loop
        End With
      Next
      
    Next j
  Next i


End Sub
 
Upvote 0
What do you mean by "names"? Cell values? - Yes, cell values.

Are you searching diagonally? Or for all cells? I am searching down the column.

Your current value is stored in C11 of the active sheet? Yes, but I need to iterate down the column of cells searching for duplicates (in that column) as I go along.

You would have to loop through each cell. This may work decently if you have a few thousand cells. You are going to run into performance/efficiency problems soon. There are much better algorithms than the quadratic one you outlined.



This would be an example of how you handle this type of situation.


Code:
Sub FindAndExecute()


  Dim i As Integer
  Dim LastRow As Long
  Dim OriginalRange, SearchRange As Range
  Dim Sh As Worksheet
  Dim Loc As Range
  Dim Search, SubSearch As String
  
  Dim i as Long, j as Long
  
  LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
  OriginalRange = ActiveSheet.UsedRange
  
  For i = 1 to OriginalRange.Rows.Count
    For j = 1 to OriginalRange.Columns.Count
      SubSearch = Cells(i, j).Value


      For Each Sh In ThisWorkbook.Worksheets
        With Sh.UsedRange
          Set Loc = .Cells.Find(What:=Mid(SubSearch, 1, 8), SearchDirection:=xlNext)
          Do Until Loc Is Nothing
            Loc.Value = "Answered!"
            Set Loc = .FindNext(Loc)
          Loop
        End With
      Next
      
    Next j
  Next i


End Sub
[/QUOTE] -Thank you for your help!!!!
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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