Error Message "Formula is too long"

[BobHat]

Hello
Using MS XP-SP1, Office XP

I execute an extract from a Lotus Notes DB via Lotus Script to Excel.
The contents of one column needs to be manually edited and also to
remove what appears to be a carriage return (shows as a small box in
the cell at the end of each line). Each cell also starts out with
"-- Modified by: ABC". When I try to do a "Find/Replace" to remove
the "-- Modified by: ABC" from each cell, I get the message that "The
formula is too long".

I have tried to change the format to "Text" before Find/Replace but
that does not work.

The cells are formated as "General"

Questions:
1. How can I Find/Replace to remove the words "-- Modified by: ABC"

2. How can I remove the apparent "Carriage Return" at the end of each
line?

Thank you

 

[Tom Ogilvy]

Dim sStr as String, cell as Range
for each cell in selection
sStr = cell
sStr = Application.Substitute(sStr,"-- Modified by: ABC","")
sStr = Application.Substitute(sStr,chr(13),"")
cell.value = sStr
Next

 

[BobHat]

Dim sStr as String, cell as Range
for each cell in selection
sStr = cell
sStr = Application.Substitute(sStr,"-- Modified by: ABC","")
sStr = Application.Substitute(sStr,chr(13),"")
cell.value = sStr
Next

Thanks Tom, it worked gerat. One minor twist, is there a way for
the macro to detect that it is at the last record and stop?

TIA

 

[Tom Ogilvy]

Well, you could only select the cells you want to process or it could stop
when it hits a blank cell

Dim sStr as String, cell as Range
for each cell in selection
if isempty(cell) then exit for
sStr = cell
sStr = Application.Substitute(sStr,"-- Modified by: ABC","")
sStr = Application.Substitute(sStr,chr(13),"")
cell.value = sStr
Next

 

[BobHat]

Well, you could only select the cells you want to process or it could stop
when it hits a blank cell

Dim sStr as String, cell as Range
for each cell in selection
if isempty(cell) then exit for
sStr = cell
sStr = Application.Substitute(sStr,"-- Modified by: ABC","")
sStr = Application.Substitute(sStr,chr(13),"")
cell.value = sStr
Next

Tom,

Last item.....

Is there a way to make this macro available to all worksheets?

TIA

Bob

 

[Tom Ogilvy]

Put it in your personal.xls in a general module.

If you don't have a personal.xls, the record a macro and excel will offer to
create one when it asks you where to store the macro. Once it is created,
you can put your code there and it will be available in
Tools=>Macro=>Macros... Select your macro and hit run. As written it works
on the selected cells.

 

[David McRitchie]

Hi Bob,
To make the macro available to any worksheet in any workbook you would
install it in your personal.xls workbook which is created in your XLSTART
directory. http://www.mvps.org/dmcritchie/excel/getstarted.htm

You would normally limit the range by restricting to your usedrange or
with the use of SpecialCells which besides being able to choose what
kind of cells automatically is restricting you to the usedrange.

Your request actually does not need a loop so left to builtin functions
of VBA or of Excel that process many cells they would run much faster
than programming a loop.

Selection.Replace What:="Modified by: ABC", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(13), Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(10),Replacement=" ", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

It would depend on your actual data if you want to replace with an empty
string or with a single space. But usually if you have a newline Chr(10)
you would want to substitute it for a space so that you have a space
between words.

However if you wanted to TRIM excess spaces the cells in addition to the
above you would need a loop. Note difference between Excel TRIM and
VBA Trim. Trim in Excel removes extra internal spaces, VBA does not.
Both VBA and Excel TRIM will remove leading and trailing spaces.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Read more about SpecialCell in
http://www.mvps.org/dmcritchie/excel/proper.htm

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm