Excel Compare Strings Macro

johnkrenkel

New Member
Joined
Apr 2, 2019
Messages
11
Just looking for a little guidance. I have a macro that functions perfect with the exception that it is slow. I added a minor piece into the macro that counts everytime there is a string match (partial string match really)

The macro uses two FOR EACH statements

For Each Cel in Rng

str1 = cel.value

For Each cel2 in rng2
str2 = cel2.value
if instr(1, str1, str2) > 0 then
i = i+1
end if
Count.value = i
next cel2

next cel

This method seems to take forever because it is taking 1 cell, then looking down the list of sheet 2 cells and finding and counting matches. There has to be a faster method but while I am getting good at For loops, I am thinking a With loop might work better or maybe even something else.

Thanks all, looking forward to seeing some responses.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,875
Office Version
  1. 2010
Platform
  1. Windows
Use For loop for comparison is known to be slow. Using array or dictionary objects may improve the speed by a factor of 100 or more.

What are rng and rng2? I mean their declaration.
 

johnkrenkel

New Member
Joined
Apr 2, 2019
Messages
11
Use For loop for comparison is known to be slow. Using array or dictionary objects may improve the speed by a factor of 100 or more.

What are rng and rng2? I mean their declaration.
My apologies for the delay.

Dim rng As Range, cel As Range ' Account List
Dim rng2 As Range, cel2 As Range ' Client Scrub

I then use a last row to determine the active range and cel then goes row by row trying to compare. They are not exact matches, actually the compare is only really looking at our master list vs the first 9 char in the daily report.
 

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,875
Office Version
  1. 2010
Platform
  1. Windows
My apologies for the delay.

Dim rng As Range, cel As Range ' Account List
Dim rng2 As Range, cel2 As Range ' Client Scrub

I then use a last row to determine the active range and cel then goes row by row trying to compare. They are not exact matches, actually the compare is only really looking at our master list vs the first 9 char in the daily report.
So, theirsizes are dynamic? Can You tell in advance whose size is larger?
 

johnkrenkel

New Member
Joined
Apr 2, 2019
Messages
11
So, theirsizes are dynamic? Can You tell in advance whose size is larger?
One sheet it pretty much static the other is dynamic / transactions. Basically it is matching clients to transactions and dumping them into separate sheets to send client specific transaction reports from a comingled master

In short it works similar to this

Sheet1, row1 - client A
Sheet 2, row 1 - if client A, then put it client A sheet else next row
Sheet 2, row 2, if client A then .....

It does this for say 15-20 clients, about 400-800 transactions a day. Takes about 10 seconds on average, but in a terminal server environment (which slows it down due to shared memory allocations)

Maybe this is the best we can do, but if we can speed it up, I would like to give it a shot
 

Forum statistics

Threads
1,141,310
Messages
5,705,677
Members
421,404
Latest member
Mikecollo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top