Count Words in Excel

[wiginprov]

I need to identify duplicate names and addresses in an Excel file.

I have the following syntax, which will highlight the specified text:


Dim strClient As String
strClient = Application.InputBox("Please enter a client name.", "Search for client name&quot

With Worksheets(1).Range("c1:c500&quot
Set c = .Find(strClient, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
c.Interior.PatternColorIndex = 28
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

Can anyone tell me how to count the number of records with the specified text, or identify duplicates, so I need not scroll through the entire file? This database will likely be populated with 5,000 records.

Many thanks.

 

[DarkSun]

Try this code then let me know if you have any problems.

Private Sub FindMatches()
Dim AryPositions() As String
Dim strClient As String
i = 1
strClient = Application.InputBox(&quot;Please enter a client name.&quot;, &quot;Search for client name&quot

With Worksheets(1).Range(&quot;c1:c500&quot
Set c = .Find(strClient, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
c.Interior.PatternColorIndex = 28
ReDim Preserve AryPositions(i)
AryPositions(i) = c.Address
i = i + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

MsgBox &quot;There were &quot; & UBound(AryPositions()) & &quot; matches found.&quot;
answer = MsgBox(&quot;Would you like to View Cells?&quot;, vbYesNo)
If answer = 6 Then
For i = 1 To UBound(AryPositions())
Worksheets(1).Range(AryPositions(i)).Select
MsgBox &quot;Continue?&quot;, vbOKOnly
Next i
End If
End Sub

 

[wiginprov]

Thank you! It works great.

I am not familiar with the &quot;ReDim Preserve....&quot; syntax or &quot;UBound&quot;. I will have to so some research so I fully benefit from your efforts.

I am most appreciative.