Count how many words contain any of specific letters

Question

I'm trying to compute the total number of words that contain any of the letters "j", "a", "d", "e".

If the word for example, was "Jacket" then it would be counted. The entire macro goes through a list of words in column A and rows 3 to 373659.

Dim count As Long
Dim word As String
Dim row As Long
    
count = 0
    
For row = 3 To 373659
    word = Cells(row, 1).Value
    If InStr(word, "j") Or InStr(word, "a") Or InStr(word, "d") Or     InStr(word, "e") Then
        count = count + 1
    End If
Next row

Is this code correct? Can it be improved?

Note that this chain of `Or`s works currently because it only involves the `Or`s. If you decide to make the conditions a bit more complicated, it may [fall apart](https://stackoverflow.com/q/24684955/11683). You need to explicitly compare the results of `InStr`s to zero to avoid that. — GSerg, Dec 14 '21 at 21:27
I would use `VBScript.RegExp` with the `.Test` method to test for existence of the regex. — HackSlash, Dec 14 '21 at 21:52
how about replacing each letter with empty string getting the count before and after and subtracting the two.. Alternatively use a case so you can early exit once one instance is found: https://stackoverflow.com/questions/7015471/does-the-vba-and-operator-evaluate-the-second-argument-when-the-first-is-false — xQbert, Dec 14 '21 at 22:10

Tim Williams · Answer 1 · 2021-12-14T21:43:10.177

This should be more efficient:

Sub Tester()
    
    Dim count As Long
    Dim word As String
    Dim row As Long, arr, e, data
        
    count = 0
    arr = Array("a", "e", "d", "j") 'ordering most-common to least-common
                                    '  will give you a slight boost
    
    data = Range("A3:A373659").Value 'read the whole range,
                                     '  not cell-by-cell
    
    For row = 1 To UBound(data, 1)
        word = lcase(data(row, 1))

        'either test letter-by-letter...
        For Each e In arr
            If InStr(word, e) > 0 Then 
                count = count + 1
                Exit For  'exit after first match: no need to test others
            End If
        Next e

        'or use `Like`
        If word Like "*[aedj]*" Then
            count = count + 1
        End If

    Next row

End Sub

Haha - of course... I always forget the `[]` version... – Tim Williams Dec 14 '21 at 21:41 — Tim Williams, Dec 14 '21 at 21:41

Count how many words contain any of specific letters

1 Answers1