String manipulation in macro runs slower each time ran.

The string manipulation is taking a long string and pasting segments of 255
characters into a cell until all the characters are in cells. After running
this about 3 times Excel slows to a halt. How do I fix this?

I'm new to this. Here is the code I'm having problems with. Task manager
reports no memory problems.

Do Until Left$(ActiveCell.Value, 1) = Chr$(12)
lins = lins + 1
If lins > 10000 Then
Exit Do
End If
If Len(ActiveCell) > 255 Then
Fcel = ActiveCell
ActiveCell.Offset(1, 0).Activate
Selection.Insert Shift:=xlDown
ActiveCell = Right(Fcel, Len(Fcel) - 255)
ActiveCell.Offset(-1, 0).Activate
ActiveCell = Left(Fcel, 255)
'ActiveCell.Offset(1, 0).Activate
Else
Fcel = ""
End If
ActiveCell.Offset(1, 0).Select
Loop


"Lamination Technology" wrote:

> The string manipulation is taking a long string and pasting segments of 255
> characters into a cell until all the characters are in cells. After running
> this about 3 times Excel slows to a halt. How do I fix this?

Without knowing the context in which you're running this, I have to
guess that you may be moving growing amounts of text each time you run
this code. Do you run it from the top of a column every time, thereby
moving all your previous text down with each pass through the loop?

BTW, in testing this in Excel 97 I had to change the first line to get
the loop to end properly:

Do Until (ActiveCell.Value) = Empty

Mark,
Thanks for the response. I'll try to give you all the information without
going overboard. Also I think that I'm about to track down the problem. I am
taking a string of data withover 1200 characters. Take the string, copy and
paste it into the next cell, back up to the previous cell, & trim the excess
of 255 characters on the right. I wind up with the data string in a column of
cells with 255 characters per cell. The reason for this, just in case
someone else runs into this problem, I am copying each character into a cell
on the next spread sheet for analysis.( Max 256 cells in a row) This is
captured code from a PLC com port.
Back to my problem----I think the problem occurs when the paste is being
done and cells are shifted down. I'm running Excel 2003. If I shut down the
spreadsheet, the open it again the problem goes away. I can run the macros
about 3 times before excel crawls to a halt. Aggrivating! Would you like to
look at the spreadsheet?


"Mark Lincoln" wrote:

> Without knowing the context in which you're running this, I have to
> guess that you may be moving growing amounts of text each time you run
> this code. Do you run it from the top of a column every time, thereby
> moving all your previous text down with each pass through the loop?
>
> BTW, in testing this in Excel 97 I had to change the first line to get
> the loop to end properly:
>
> Do Until (ActiveCell.Value) = Empty
>
>

I don't want to open unknown files except as a last resort. Let's see
if we can solve this first.

We're thinking the same way in that we believe the continual shifting
down of cells is causing the problem.

Do you *have* to shift cells? Can you just move excess text to the
next cell down until you've finished? I worked up an example that
works this way. But I only tested it with short strings and chopped
them up into 10-character lengths. If it will work for you without
hosing the rest of your workbook, modify it as needed to fit your
situation and see if it keeps Excel from getting cranky.

Option Explicit

Sub ChopText()
'Assumes the user has activated
'the cell containing the text
'to be manipulated

Dim R As Integer
Dim C As Integer
Dim RemainingLength As Integer
Dim ExcessText As String

R = ActiveCell.Row
C = ActiveCell.Column

Do
If R > 10000 Then Exit Do
If Len(Cells(R, C).Value) > 255 Then
ExcessText = Right(Cells(R, C).Value, Len(Cells(R, C)) - 255)
Cells(R, C).Value = Left(Cells(R, C).Value, 255)
R = R + 1
Cells(R, C).Value = ExcessText
RemainingLength = Len(ExcessText)
End If
Loop Until RemainingLength <= 255

End Sub

I'd recommend fetching the content of the cell, manipulating the
string, and then saving to the cell value.

Each time you access a cell internally Excel has to access a data
structure, find the string, and then modify it.

Left, Right, Len those operations apply to Strings just as they do to
Cells.

Here is the code I'm having trouble with. Task manager reports no memory
issues.
Thanks.


Do Until Left$(ActiveCell.Value, 1) = Chr$(12)
lins = lins + 1
If lins > 10000 Then
Exit Do
End If
If Len(ActiveCell) > 255 Then
Fcel = ActiveCell
ActiveCell.Offset(1, 0).Activate
Selection.Insert Shift:=xlDown
ActiveCell = Right(Fcel, Len(Fcel) - 255)
ActiveCell.Offset(-1, 0).Activate
ActiveCell = Left(Fcel, 255)
'ActiveCell.Offset(1, 0).Activate
Else
Fcel = ""
End If
ActiveCell.Offset(1, 0).Select
Loop

"Lamination Technology" wrote:

> The string manipulation is taking a long string and pasting segments of 255
> characters into a cell until all the characters are in cells. After running
> this about 3 times Excel slows to a halt. How do I fix this?