upobDaPlaya
asked on
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 )
Match Example on Partial String
=INDEX(G:G,MATCH("*"&H1&"*
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
@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.
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.
ASKER
Very nice..quite resourceful
upobDaPlaya,
In your real workbook, did the VBA function achieve any improvements in recalc time?
Brad
In your real workbook, did the VBA function achieve any improvements in recalc time?
Brad
ASKER
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
@upobDaPlaya
Are you saying that the performance is tolerable or wonderful?
Are you saying that the performance is tolerable or wonderful?
ASKER
The new performance is great..Old performance I was able to go to DD and get a latte :)
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
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
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.
Open in new window
Thanks,Mark