Using .find for multiple strings VBA

Question

Is it possible to use .find for more than one string at a time? For example,

worksheet.find(What:="String1", "String1", "String3", etc...)

It doesn't work now, because it's expecting the After parameter, but I need to check the sheet for a certain set of strings to accurately determine the number of rows I need to read through (my macro grabs data from a workbook and posts it into another workbook).

I know I could always just use a giant If Or statement and loop through all the rows and columns, I just figured using .find would be a bit cleaner.

Do you want the first cell that contains any of the strings or do you want the full set of cells?? — Gary's Student
– Gary's Student, Commented Feb 24, 2014 at 16:33
The first cell that contains any. The excel sheets I'm getting the data from all have the values in different places, so I just need the first instance to get how many rows need to have data pulled from. — PointXIV
– PointXIV, Commented Feb 24, 2014 at 16:34

CRondao · Accepted Answer · 2014-02-24 17:10:29Z

1

You cannot do it with .find and .find is very inefficient, loop with IF, and, if you want to avoid a big OR do:

if instr("st1#st2#st3#...",cells(..,..)) & "#")>0 then

Cell value "Paul"

Match

MsgBox InStr("Peter#Paul#Ringo#", "Paul" & "#")

No Match

MsgBox InStr("Peter#Paul#Ringo#", "Pau" & "#")

No Match

MsgBox InStr("Peter#Paul#Ringo#", "Paul Mc" & "#")

edited Feb 24, 2014 at 17:10

answered Feb 24, 2014 at 16:42

CRondao

1,9012 gold badges12 silver badges10 bronze badges

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

9 Comments

PointXIV Over a year ago

Yeah, I guess I'll just have to do a loop with If Orthen. Can't do instr, because I need a direct match. Thanks!

CRondao Over a year ago

What do you mean, why can't you use instr with a direct match?

PointXIV Over a year ago

Doesn't instr check if the string is in the entire cell, instead of a direct match? There's a possibility that sheet will have cells that read "'String1' More Text" and also just "String1", and I only want it to match if it's "String1".

CRondao Over a year ago

Well yes and no. :) In this case you're matching cell value(second arg) against the list of values (first arg). as long as you write in the first arg the exact match ("String1#String2#...") it will work. The # is to make sure that will not happen.

PointXIV Over a year ago

Would that be more efficient than an If Or? The last problem I'm having is getting it to search the entire workbook until it finds it.

|

George · Accepted Answer · 2014-02-24 16:56:12Z

0

You can use a double for loop where the outer for loop will iterate though all of the cells and the inner for loop searches each cell for the strings you are look at and uses the .find function. It will find the first occurance and you can do whatever you want.

set rng = [range you are searching] (assuming this is one column, can adjust code for 2d array by adding another for loop) strings[] = array of your search strings

For i = 1 to rng.cells.count
    For j = 0 to strings.count
        found = instr(strings[j],rng.cells(i,1)
        If found >0 then
            'whatever you want to do
        Endif
    Next j
Next i

answered Feb 24, 2014 at 16:56

George

1,49917 silver badges29 bronze badges

1 Comment

PointXIV Over a year ago

That's what I've been doing so far, was just going to see if there was a faster, cleaner way to do so. There's about 14 Ors in the if statement.

Collectives™ on Stack Overflow

Using .find for multiple strings VBA

2 Answers 2

9 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related