Formula too long

[Todd]

I have a formula that links 12 different worksheets, that
are on a network drive, to a single cell. I am trying to
use the Edit Replace function and replace 2003 with 2004
for the upcoming year however i get a formula too long
error. What can I do to get around this and replace this
number? Thanks for the help.

Todd

 

[Debra Dalgleish]

If the workbooks are open the formula will be shorter, because it won't
include the file path in the reference. That may reduce the formulas
sufficiently to allow the Edit>Replace.

 

[Dave Peterson]

And sometimes the formula isn't too long--except in Edit|Replace's eyes.
(replacing 2003 with 2004 shouldn't take many more characters(?).)

Maybe you could just loop through the cells:

Option Explicit
Sub testme01()

Dim myCell As Range
Dim myRng As Range

On Error Resume Next
Set myRng = Intersect(Selection, ActiveSheet.UsedRange)
On Error GoTo 0

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

For Each myCell In myRng.Cells
myCell.Formula = Replace(myCell.Formula, "2003", "2004")
Next

End Sub

Be careful and select just the cells that should be updated (if you have some
2003's that shouldn't be changed!)

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