VBA - Search a string for all values in array

Question

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!

You could make it faster if you put the range in an array also, so you are not going back to the sheet so many times. Or at least put the value of each cell in a variable and test that, it will also cut back on the number of times you reference a cell on the sheet. — Scott Craner
– Scott Craner, Commented Jun 16, 2016 at 14:30
Interacting with the worksheet (Using Cells() in macro) multiple times slows the macro down. You can save the range to be searched in an array and then use array in macro to increase the speed. And some basic things such as keep calculations on manual mode and turn off screen updating. — Ethun_Hunt
– Ethun_Hunt, Commented Jun 16, 2016 at 14:39
I'd say it's fine. If you want to try to eek out a very tiny bit of performance, you can try saving the value of the Cell into a string variable so it doesn't have to keep referencing the cell over and over. — Jeremy
– Jeremy, Commented Jun 16, 2016 at 14:52

Yarnspinner · Accepted Answer · 2016-06-16 14:31:54Z

1

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

answered Jun 16, 2016 at 14:31

Yarnspinner

8925 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA - Search a string for all values in array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related