PDA

View Full Version : Help with .Find and .FindNext in VBA



csinger22
11-17-2015, 09:31 AM
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,3) and comparing it to all other string values in that column of 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,3), then searching for duplicates to cell (2,3) and so on through the entire column in the worksheet. Right now my code only words for finding all the dupes for a specific string defined by me. How do I iterate through one entire column in the 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
Next j
Next i


End Sub

mancubus
11-19-2015, 02:54 AM
welcome to the board.

post your workbook:
Go Advanced, Manage Attachments, Add Files, Select Files, (select the file/s and click Open), Upload Files, Done
before posting it alter sensitive/confidential data.

this way i may understand what you mean by "I am not searching just for one string"

(i understand) you want to replace all substrings in all worksheets (other than active sheet i guess) with "Answered"?