Excel Compare Strings Macro

[johnkrenkel]

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.

 

[yky]

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]

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]

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]

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