Help with .Find and .FindNext in VBA

[csinger22]

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

 

[iliace]

[/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.

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

 

[csinger22]

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.

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!!!!