ExcelBanter - Macro problem

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Macro problem (https://www.excelbanter.com/excel-discussion-misc-queries/124287-macro-problem.html)

Macro problem

(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

Stefi

Macro problem

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

€ ezt Ã*rta:

(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

Bob Phillips

Macro problem

Either that, or remove the End If after.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

"Stefi" wrote in message
...
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

" ezt írta:

(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

[email protected]

Macro problem

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
-=====================
Stefi wrote:
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

" ezt írta:

(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

Jim Rech

Macro problem

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
wrote in message
oups.com...
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
-=====================
Stefi wrote:
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

" ezt írta:

(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

Dave Peterson

Macro problem

Cel.formula will include the leading equal sign.

Maybe...

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

wrote:

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
-=====================
Stefi wrote:
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

" ezt írta:

(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

--

Dave Peterson

[email protected]

Macro problem

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

-John
-----------------------
Jim Rech wrote:
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
wrote in message
oups.com...
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
-=====================
Stefi wrote:
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

" ezt írta:

(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

All times are GMT +1. The time now is 03:46 PM.