Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-02-14
10
Medium Priority
?
4,888 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 81

Accepted Solution

by:
byundt earned 2000 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

1
 
LVL 46

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 46

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 46

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

810 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