Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Message "Formula is too long"
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Message "Formula is too long"
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 -- Regards, Tom Ogilvy wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Message "Formula is too long"
On Mon, 27 Sep 2004 10:29:47 -0400, "Tom Ogilvy"
wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Message "Formula is too long"
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 -- Regards, Tom Ogilvy wrote in message ... On Mon, 27 Sep 2004 10:29:47 -0400, "Tom Ogilvy" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Message "Formula is too long"
On Mon, 27 Sep 2004 14:49:21 -0400, "Tom Ogilvy"
wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Message "Formula is too long"
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. -- Regards, Tom Ogilvy wrote in message ... On Mon, 27 Sep 2004 14:49:21 -0400, "Tom Ogilvy" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Message "Formula is too long"
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 wrote in message ... On Mon, 27 Sep 2004 14:49:21 -0400, "Tom Ogilvy" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel error: "The path you entered, ---------- , is too long. " | Excel Discussion (Misc queries) | |||
"Formula is to long" error. | Excel Discussion (Misc queries) | |||
Error message "formula is too long" | Excel Discussion (Misc queries) | |||
Error message "formula is too long" | Excel Worksheet Functions | |||
"string too long" error message | Excel Discussion (Misc queries) |