Solved

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

Posted on 2014-02-14
10
4,050 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 81

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 81

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
LVL 81

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 81

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

749 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