In the VBA Loop, the process checks one cell at the time, causing the slower
operation time. The REPLACE option locates every instance simultaneously and
makes the replacements all at the same time.
--
Best wishes,

Jim


"Myles" wrote:


Can someone explain why the use of REPLACE in VBA is lightening fast
relative to normal looping? The marked disparity in the speed of
execution of the following 2 codes highlights the point.


Sub ReplaceFunction()
Range("A1: BQ5000").Replace, "ALPHA", "BETA", xlWhole
End sub

Sub ReplaceViaLoop()
Dim c as Range

For each c in Range("a1:bq5000")
if c.value="ALPHA" then
c,value ="BETA"
end if
next

End Sub

Surely the underlyng code of the REPLACE function must be attempting
some looping, whatever the language used (VB6?). I don't wish to
believe that VBA is intrincically slower than VB6.

Myles


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=572341