Find function to find multiple strings

Question

I have a column with a couple possible strings, and I want to know in which row they're.

Dim lx As Integer
Dim FoundExec As Range
With Worksheets("Load " & LoadNr(1)).Range("G10:G26")
    Set FoundExec = .Find("Exec", LookIn:=xlValues)
    If Not FoundExec Is Nothing Then
        Dim FirstLoadAddress As String
        FirstLoadAddress = FoundExec.Address
        Do
            lx = FoundExec.Row
            Set FoundExec = .FindNext(FoundExec)

            'Code...

            Loop While FoundExec.Address <> FirstLoadAddress
    End If
End With

This works for me as it's supposed to, but I want also to find the values "OVS", "OV" and "OS", as the same code is to follow after those inputs.

I treid

Dim lx As Integer
Dim FoundExec As Range
Dim FoundOVS As Range
Dim FoundOV As Range
Dim FoundOS As Range
Dim AllOSS As Range

With Worksheets("Load " & LoadNr(1)).Range("G10:G26")
    Set FoundExec = .Find("Exec", LookIn:=xlValues)
    Set FoundOVS = .Find("OVS", LookIn:=xlValues)
    Set FoundOV = .Find("OV", LookIn:=xlValues)
    Set FoundOS = .Find("OS", LookIn:=xlValues)

    Set AllOSS = Application.Union(FoundExec, FoundOVS, FoundOV, FoundOS)
    If Not AllOSS Is Nothing Then
        Dim FirstLoadAddress As String

        'Code...

        FirstLoadAddress = AllOSS.Address
        Do
            lx = AllOSS .Row
            Set AllOSS = .FindNext(AllOSS)
            Loop While AllOSS.Address <> FirstLoadAddress
    End If
End With

But that turns in ongoing loop.

Is there a way to do that quickly? and why turns the second in ongoing loop?

The problem seems to be that you cannot .Union multiple searches and then do a .FindNext on it. Check your code with a Debug.Print AllOSS.Address just after the Do then you'll understand why Do Loop will never exit. — simpLE MAn
– simpLE MAn, Commented Feb 18, 2014 at 15:28

CRondao · Accepted Answer · 2014-02-18 15:28:47Z

1

Forget all those Finds, Find is very, very inefficient...

Do like this:

With Worksheets("Load " & LoadNr(1)).Range("G10:G26")
 nr = .Rows.Count
 For r = 1 To nr
  If InStr("#Exec#OVS#OV#OS#", .Cells(r, 1) & "#") > 0 Then
   ' do whatever you need to do
  End If
 Next
End With

Even application.match is much more efficient than .Find

answered Feb 18, 2014 at 15:28

CRondao

1,9012 gold badges12 silver badges10 bronze badges

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

8 Comments

CRondao Over a year ago

hehe...with 4 .FINDS? That's what you think try it, time it, and then talk....I did it and is much faster.... much, much faster... :) (and by the way what do you think .FIND is doing?)

Gary's Student Over a year ago

+1 nice way of implementing the OR

simpLE MAn Over a year ago

It is not coded in VBA for sure... Even if this is quicker, there is a slight chance it does it like this.

CRondao Over a year ago

Again, the only thing I can say to you is to time it and than talk....but with 4 FINDS, will be, probably, 3 to 4 times slower :)

simpLE MAn Over a year ago

@CRondao, I do not say the contrary, I am just referring to your [...](and by the way what do you think .FIND is doing?)

|

Collectives™ on Stack Overflow

Find function to find multiple strings

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related