Finding a Partial Text in a Range using VBA - Match function

Is there a quicker way of doing the equivalent Match function in VBA ?  See link for the example.  This function seems to take quite a bit of time.

Match Example on Partial String

=INDEX(G:G,MATCH("*"&H1&"*",G:G,0)+3)
upobDaPlayaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
byundtConnect With a Mentor Commented:
barryhoudini's formula can potentially search a million rows of data. Worse yet, each cell searched involves stepping one character at a time through the entire cell contents to make sure that the value of cell H1 isn't embedded in that cell's text. It's an expensive computation, no matter how you slice it.

I don't believe VBA will help. You still need to troll all the cells and every possible starting point in each cell's text. Put this UDF in a regular module, then test it with a worksheet formula like:
=INDEX(G:G,Matcher(H1,G:G)+3)
Function Matcher(vSeek As Variant, rg As Range) As Variant
Dim cel As Range
Matcher = "#N/A"
Set cel = rg.Find(vSeek, LookAt:=xlPart, MatchCase:=False)
If Not cel Is Nothing Then Matcher = cel.Row - rg.Row + 1
End Function

Open in new window

1
 
aikimarkCommented:
@Brad
I like your Matcher() function.  If you get a chance, I would be interested in any performance differences between your original Matcher() and this version.  I don't have any data that might be able to stress-test it.
Function Matcher(vSeek As Variant, rg As Range) As Variant
    Static cel As Range
    Set cel = rg.Find(vSeek, LookAt:=xlPart, MatchCase:=False)
    If cel Is Nothing Then 
        Matcher = cel.Row - rg.Row + 1
    Else
        Matcher = "#N/A"
    End If
End Function

Open in new window

Thanks,
Mark
0
 
byundtCommented:
Mark,
I don't think you would want to use Static cel, as the more common usage for a Match-like function is in multiple places in a worksheet. If cel is static, then any "not found" situation would be returned as the previous call.

Brad
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
aikimarkCommented:
@Brad

No.  You are assigning a value with every invocation with the Set statement (result of the .Find method).  The Static declaration eliminates the memory cleanup and deallocation (Garbage Collection) for Dim-declared variables/objects.
0
 
upobDaPlayaAuthor Commented:
Very nice..quite resourceful
0
 
byundtCommented:
upobDaPlaya,
In your real workbook, did the VBA function achieve any improvements in recalc time?

Brad
0
 
upobDaPlayaAuthor Commented:
Sorry byundt for the late response..it did help quite a bit..before I would be watching the lower right hand part of the screen as it slowly updated the % completion of the recalculation...not anymore..thanks for your follow-up
0
 
aikimarkCommented:
@upobDaPlaya

Are you saying that the performance is tolerable or wonderful?
0
 
upobDaPlayaAuthor Commented:
The new performance is great..Old performance I was able to go to DD and get a latte :)
0
 
byundtCommented:
upobDaPlaya,
aikimark and I were having a conversation in hidden Comments about whether declaring cel as Static would make the Matcher function even faster. Neither one of us has enough data to be able to measure the difference--but it sounds as if you do.

In case you would like to add to the discussion with benchmark data instead of gut feelings, I unhid aikimark's code in http:/Q_28365515.html#a39864096 (plus discussion in the two Comments following that one). Who knows, his tweak might be another quantum improvement for you.

Brad
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.