VBA Fastest way to find all occurrences of a string in a range

[jscranton]

Suppose I have a list of words (a rather large list of words ~55k) and I want to find the number of cells in a given range (a very large range) that contains at least one instance of that word. Is there a faster way than looping through every cell in the range and using the INSTR() function?

For i = LBOUND(words) to UBOUND(words) ' where words() contains my 55k words

  wordToSearch = words(i)

   For x = 1 to lastRowofRange

    If INSTR(1,CSTR(Range("A" & x).Value,wordToSearch) > 0 then count = count+1

   Next x
Next i

 

[Momentman]

Would a COUNTIF not be better and using wildwards

 

[JLGWhiz]

You didn't specify the range, but this method might be a little faster than a For...Next loop with InStr

Sub findNCountStuff()
Dim sh As Worksheet, dsh As Worksheet, fn As Range, fAdr As String, i As Long, words As Variant
Set sh = ActiveSheet
Set dsh = Sheets(2)
words = Array("test1", "test2", "test3")
For i = LBound(words) To UBound(words)
    With sh
        Set fn = sh.UsedRange.Find(words(i), , xlValues, xlPart)
            If Not fn Is Nothing Then
                fAdr = fn.Address
                Do
                    cnt = cnt + 1
                    Set fn = sh.UsedRange.FindNext(fn)
                Loop While fn.Address <> fAdr
                dsh.Cells(Rows.Count, 1).End(xlUp)(2) = words(i)
                dsh.Cells(Rows.Count, 1).End(xlUp).Offset(, 1) = cnt
                cnt = 0
            End If
    End With
Next
End Sub

The falacy of this method is that using the xlPart criteria makes it subject to finding pieces of larger strings.

 

[jscranton]

JLGWhiz,

Thanks. I will give it a go tomorrow on a sample and let you know what I find.

Also, I am not worried about the xlPart as these are always words separate by a space so i can build that into the find.

Thanks,

J