Limits of Substitute and Replace functions

[deutz]

Hi and thanks in advance,

I am using Excel 2003

I have a sheet that has in column A some long strings and I want a quic
way, preferably without looping, to replace some characters with anothe
character.

The problem I have run into is that when I use Replace in VBA it is onl
applied to cells with no more than 1024 characters and so I tried usin
Substitute but that crashes if there is more than 1271 characters in
cell so I don't know how to process these long strings quickly.

I tried the following and hit the limits mentioned above:


Code
-------------------

Range("A:A").Select
Selection.Replace What:=strOld, Replacement:=strNew, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

 

[Auric__]

I am using Excel 2003

I have a sheet that has in column A some long strings and I want a quick
way, preferably without looping, to replace some characters with another
character.

The problem I have run into is that when I use Replace in VBA it is only
applied to cells with no more than 1024 characters and so I tried using
Substitute but that crashes if there is more than 1271 characters in a
cell so I don't know how to process these long strings quickly.

I tried the following and hit the limits mentioned above:

If the Replace function is in 2003, you can do this:
Selection.Value = Replace(Selection.Value, strOld, strNew)

In 2007 it works for me with a 32,768-character string. (I didn't test it
further than that.)

If the Replace function is *not* in 2003, you can use Split and Join:
Selection.Value = Join(Split(Selection.Value, strOld), strNew)

Also works for me with the same 32,768-character string. I *believe* it
should work as far back as Excel 2000.

 

[Auric__]

Thanks for your suggestions Auric,

I substituted strOld and strNew with my strings and added code line
Range("A:A").Select just before your line of code

I then tried both methods but got a Type Mismatch error each time.

That's because they both expect a single string, i.e. a single cell. Try this
instead:
Dim cell As Range
For Each cell In Selection
cell.Value = Replace(cell.Value, strOld, strNew)
Next

 

[deutz]

deutz wrote:
-

That's because they both expect a single string, i.e. a single cell. Tr
this
instead:
Dim cell As Range
For Each cell In Selection
cell.Value = Replace(cell.Value, strOld, strNew)
Next

Thanks, I have similar code that loops thru each cell and works ok bu
thought there may be a quicker way to do it without looping, in one fel
swoop

 

[GS]

Thanks, I have similar code that loops thru each cell and works ok but

thought there may be a quicker way to do it without looping, in one fell
swoop.

It will always be slower to act directly on the cells. It will be
orders of magnitude faster to 'dump' the range into an array, loop the
array (in memory), then 'dump' the array back into the range.

Example:
Sub ReplaceData()
Dim vData As Variant, i As Long
vData = Selection
For i = LBound(vData) To UBound(vData) 'assumes 1 column selected
vData(i, 1) = Replace(vData(i, 1), strOld, strNew)
Next 'i
Selection = vData
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion