Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    102

    Unanswered: Error: "Formula is too long"

    Hi,
    I have the following code.

    'delete all control/special characters from Problem Details field
    Worksheets("Actual_Details").Columns("E").Replace _
    What:=Chr(9), Replacement:=" ", _
    SearchOrder:=xlByColumns, MatchCase:=False


    I have dumped some data into column E and I need to clean up all carriage returns and tabs. Some cells contain too many characters, but the code does still work for these cells.
    I execute this code on a number of different workbooks.

    It works correctly for a few books,
    but on the 4th one, i get the error "Formula is too long". Even after I have shortened the sizes of the fields.

    Anyone know how to solve this ?

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Some cells contain too many characters, but the code does still work for these cells.
    How are you determining this?

    Is there anything else about the 4th workbook that is different?



    Just as a note, you are replacing with a space, not "nothing"

    You have: Replacement:=" " (which puts a space in there)

    Do you want: Replacement:="" (which leaves no space)
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    Keep in mind that while a cell may contain 32,767 characters (bit only 1,024 characters will display in the cell, the rest in the formula bar); but if a formula is used then the max limit is 1,024 characters.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    Sep 2003
    Posts
    102
    1. Oh ya, sorry... I replace it with a space.

    2. The column which i do the replacing does not use a formula.

    3. I tried shorting the size of each cell to 1024 characters. Same error:
    1023 characters - same error
    1000 characters - same error
    900 characters - works fine.

    Do you know why that would be ?

    Some of the cells in the first three workbooks have characters exceeding the maximum, but the replace function was able to work correctly for these.

    Thx


    Originally posted by shades
    Keep in mind that while a cell may contain 32,767 characters (bit only 1,024 characters will display in the cell, the rest in the formula bar); but if a formula is used then the max limit is 1,024 characters.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •