CODEWITHSUNDEEP
arraysexcelvba
Justin_GM
Justin_GM

Reputation: 3

VBA - Search a string for all values in array

Just an efficiency question here. I'm essentially looping through cells in a column to see if they contain a specific string, but then that cell also needs to not include any of 14 different strings. My current solution is to find the string, and then loop through the array using instr on the cell. However, this will happen possibly hundreds of times when the macro is running. I'm curious if there is a better way.

For instance:

NotArr = Array("blah1", "blah2", "blah3", "etc")

For r = 1 to 10
    'Let's assume I've already found the first string
    For i = 1 to 4
        If Not InStr(Cells(r, 1).value, NotArr(i)) > 0 Then
            'Do things
        End If
    Next i
Next r

Note: I know I'm probably overthinking or just missing the obvious. I've been buried in VBA for about 6 weeks @ 10 hour days and can feel my brain melting.

Thanks all!

Upvotes: 0

Views: 2381

Answers (1)

Yarnspinner
Yarnspinner

Reputation: 892

Instead of looping through the cells, which requires VBA to interact with the Excel range hundreds of times, why not try storing the data in an array first? This should speed things up considerably.

Dim arr() as Variant
arr = Range("A1:G10")
For each rng in arr
    For i = 1 to 4
        If Not InStr(rng.value, NotArr(i)) > 0 Then
            'Do things
        End If
    Next i
Next

Upvotes: 1

Related Questions