When using CtrlF, told formula too long

[jstaines]

I am working in Excel 2003, I have 4 spreadsheets 1st QTR thru 4th QTR
and then another sheet which is a summary. The summary report is
readng info from the QTR reports. Now I would like to Copy QTR 1 to
QTR 2 and do a control find 1st Q and replace with 2nd Qtr, but it
tells me that some formulas are too long - is there any way around
this?
Thanks

 

[Peo Sjoblom]

Remove the equal sign before you do the replace, then after you have done it
put it back

 

[jstaines]

I tried that, and it didn't work - any other suggestions??

 

[Peo Sjoblom]

Maybe you have spaces in your replace with box thus making more characters

 

[Debra Dalgleish]

If you're trying to replace 1st Q with 2nd Qtr, you're adding two
additional characters in each replacement.

You could try replacing 1st Q with 2nd Q, or with Qtr2, to keep it as
short as possible.

 

[Harlan Grove]

(e-mail address removed) wrote...

I am working in Excel 2003, I have 4 spreadsheets 1st QTR thru 4th QTR
and then another sheet which is a summary. The summary report is
readng info from the QTR reports. Now I would like to Copy QTR 1 to
QTR 2 and do a control find 1st Q and replace with 2nd Qtr, but it
tells me that some formulas are too long - is there any way around
this?

What's the original formula? Maybe there's a way to shorten it by a
lot. Also, if by 'spreadsheets' you mean separate workbooks/files, and
if any are closed, your formula references to ranges in those files
will include full pathnames. If that's the case, open BOTH the 1st AND
2nd quarter files, then try Edit > Replace on these formulas. If the
files are closed and Excel is complaining about overly long formulas,
you'll only be able to change the formulas if the files are open.

Note: I wouldn't have to guess about this possibility if you had
included a sample original formula in your original post.

 

[Dave Peterson]

And sometimes you get that error and you're not even working with formulas!

If that's the case--you're just updating text values in your worksheet, how
about a little macro:

Option Explicit
Sub testme01()

Dim FoundCell As Range
Dim ConstCells As Range
Dim BeforeStr As String
Dim AfterStr As String

BeforeStr = "1st Q"
AfterStr = "2nd Qtr"

With ActiveSheet
Set ConstCells = Nothing
On Error Resume Next
Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _
xlTextValues)
On Error GoTo 0

If ConstCells Is Nothing Then
MsgBox "Select some cells in the used range"
Exit Sub
End If

With ConstCells
'get as many as we can in one step
.Replace what:=BeforeStr, Replacement:=BeforeStr, _
lookat:=xlPart, SearchOrder:=xlByRows

Do
Set FoundCell = .Cells.Find(what:=BeforeStr, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlPart, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'done, get out!
Exit Do
End If
FoundCell.Value _
= Replace(FoundCell.Value, BeforeStr, AfterStr)
Loop
End With
End With

End Sub



If you're using xl97, change that Replace( to application.substitute(

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm