Macro problem

[johnsr4]

(Excel 2003) Can anyone help with a quick macro I have that is giving
me an error?
The purpose of the macro is to find all formulas on a page (range) and
increment the Column by 1. The error is on the line "cel.formula =
prefix & cel.formula & sufix"

The formulas I am changing are all references to another worksheet. Is
the problem that the formula contains an equal sign in it?

Any help is GREATLY appreciated!
Thanks
-John
--------------------------------------------------------------------------------

Sub EditFormula()
Dim Cel As Range
Dim Sufix As String

Dim Prefix As String
Prefix = "=Offset("
Sufix = ", 0, 1)"
For Each Cel In Selection
If Cel.HasFormula Then _
Cel.Formula = Prefix & Cel.Formula & Sufix
End If
Next Cel
End Sub

 

[Guest]

uI didn't test it, but _ (underline) character in line

If Cel.HasFormula Then _

is a wrong syntax, try your macro without it:
If Cel.HasFormula Then

Regards,
Stefi

„[email protected]†ezt írta:

 

[Bob Phillips]

Either that, or remove the End If after.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[johnsr4]

Thanks for the replies. I've tried both modifications, but still get
the error:
"Application-defined or object-defined error" (for the same line)
So the macro (now) looks like this:

Sub EditFormula()
Dim Cel As Range
Dim Sufix As String

Dim Prefix As String
Prefix = "=Offset("
Sufix = ", 0, 1)"

For Each Cel In Selection
If Cel.HasFormula Then
Cel.Formula = Prefix & Cel.Formula & Sufix
End If
Next
End Sub

any ideas?
Thanks

-John
-=====================

 

[Jim Rech]

One problem at least is that the formulas selected have "=" in front of
them. So to remove them:


Cel.Formula = Prefix & Mid(Cel.Formula, 2) & Sufix

This assumes that Mid(Cel.Formula, 2) returns a legitimate cell reference.

--
Jim
Thanks for the replies. I've tried both modifications, but still get
the error:
"Application-defined or object-defined error" (for the same line)
So the macro (now) looks like this:

Sub EditFormula()
Dim Cel As Range
Dim Sufix As String

Dim Prefix As String
Prefix = "=Offset("
Sufix = ", 0, 1)"

For Each Cel In Selection
If Cel.HasFormula Then
Cel.Formula = Prefix & Cel.Formula & Sufix
End If
Next
End Sub

any ideas?
Thanks

-John
-=====================

 

[Dave Peterson]

Cel.formula will include the leading equal sign.

Maybe...

Cel.Formula = Prefix & mid(Cel.Formula,2) & Sufix

 

[johnsr4]

THANK YOU (both of you)!!!!!!!!!!!!!!!!!!!
That did it. Nothing like saving a TON of manual editing.
Thanks again.

-John
-----------------------