Need faster macro

Posted by Celia on September 16, 2000 5:48 AM

The following code takes about 10 minutes to run.
There are 2,542 cells in range1 and 91 things in range2.
Can the code be changed to make it quicker?

For Each cell In range1
For Each thing In range2
cell.Replace What:=thing, Replacement:=thing2, LookAt:=xlWhole
Next
Next

Celia

Posted by kaiowas on September 18, 0100 3:18 AM

Re: Done

Assuming that range2 is actually a range of cells from which you are reading values, if you copy the contents of this range into an array and then read from the array instead of from the cells you should get even more of a speed up. This works because interacting with a cell is a lot slower than interacting with a variable.

Posted by Celia on September 18, 0100 3:45 AM

Re: Done

kaiowas
Thanks. In theory you are right but I did that and it also took exactly 16 seconds with the IF statement and 10 minutes and 39 seconds without the IF statement( i.e. the same as not putting the values in an array)
Celia

Posted by Celia on September 18, 0100 4:06 AM

Re: Done

I checked again. You are right - by putting the range2 values in an array, the run time is reduced from 16 seconds to 11 seconds.
Celia

Posted by Celia on September 16, 0100 8:22 PM

Done

Reduced the run-time to 16 seconds by changing to :-

For Each cell In range1
For Each thing In range2
If Len(cell) = Len(thing) And Left(cell, 1) = Left(thing, 1) Then
cell.Replace What:=thing, Replacement:=thing2, LookAt:=xlWhole
End If
Next
Next

Can't imagine why I didn't think of that in the first place.

Celia