array instead of replace feature?

[Guest]

Is it possible to use an array to load a range, find a value and replace it
with another value, and return the range back to the original cells?
Using the find/replace feature is very slow.
Thanks!

 

[Gary Keramidas]

1. how big of a range are you talking about?
2. find is very fast in my opinion, post some code.

turn off calculation and screenupdating, execute your find, then turn it back
on.

 

[NickHK]

Whilst you can achieve the same in VBA:

Private Sub CommandButton3_Click()
Dim arrRange As Variant
Dim i As Long, j As Long

arrRange = Range("A1:B4")

For i = LBound(arrRange, 1) To UBound(arrRange, 1)
For j = LBound(arrRange, 2) To UBound(arrRange, 2)
arrRange(i, j) = Replace(arrRange(i, j), " & ", "&")
Next
Next

Range("A1:B4") = arrRange

End Sub

I cannot see this would be faster than
Range("A1:B4").Replace " & ", "&"

Or are you doing something else ?

NickHK

 

[Guest]

Hi Nick,

The 'Private Sub CommandButton3_Click()' is very fast and is perfect!
It's actually much much faster than: Range("A1:B4").Replace " & ", "&"

The range I have is very large and it's only part of the procedure.

How did you learn so much about arrays?

Thanks,

Gunther