Macro problem

J

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
 
S

Stefi

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:
 
B

Bob Phillips

Either that, or remove the End If after.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
J

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
-=====================
 
J

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
-=====================
 
D

Dave Peterson

Cel.formula will include the leading equal sign.

Maybe...

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

johnsr4

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top