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!

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.

--


Gary


"MasOMenos" wrote in message
...
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!

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

"MasOMenos" wrote in message
...
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!

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

"NickHK" wrote:

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

"MasOMenos" wrote in message
...
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!