Solved

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

Posted on 2014-02-14
10
3,595 Views
Last Modified: 2014-02-28
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)
0
Comment
Question by:upobDaPlaya
  • 4
  • 3
  • 3
10 Comments
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 39860857
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

0
 
LVL 45

Expert Comment

by:aikimark
ID: 39864096
@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
 
LVL 80

Expert Comment

by:byundt
ID: 39865096
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
 
LVL 45

Expert Comment

by:aikimark
ID: 39865219
@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
 

Author Closing Comment

by:upobDaPlaya
ID: 39866364
Very nice..quite resourceful
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 80

Expert Comment

by:byundt
ID: 39866388
upobDaPlaya,
In your real workbook, did the VBA function achieve any improvements in recalc time?

Brad
0
 

Author Comment

by:upobDaPlaya
ID: 39893753
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
 
LVL 45

Expert Comment

by:aikimark
ID: 39894101
@upobDaPlaya

Are you saying that the performance is tolerable or wonderful?
0
 

Author Comment

by:upobDaPlaya
ID: 39896264
The new performance is great..Old performance I was able to go to DD and get a latte :)
0
 
LVL 80

Expert Comment

by:byundt
ID: 39896366
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now