Change formula to Indirect
 

I need to change a lot of formulas to "Indirect".

Example, A1, =B1 - Need to change it to =Indirect("B1")

I have been trying find and replace;

Find =, Replace XIndirect("
This produces XIndirect("B1

How can I Find the end of formula and add, ")

Any ideas on how to add to the end of the formula or any other way of
changing a formula to indirect would be appreciated.

Sub change_formula()
For c = 1 To 5
For r = 1 To 10
Cells(r, c).Formula = "=indirect(""" & Right(Cells(r, c).Formula,
Len(Cells(r, c).Formula) - 1) & """)"
Next r
Next c
End Sub

--
Ian
--
"Ronbo" wrote in message
...
I need to change a lot of formulas to "Indirect".

Example, A1, =B1 - Need to change it to =Indirect("B1")

I have been trying find and replace;

Find =, Replace XIndirect("
This produces XIndirect("B1

How can I Find the end of formula and add, ")

Any ideas on how to add to the end of the formula or any other way of
changing a formula to indirect would be appreciated.

Ian -

Thanks a lot, that is exactly what I need. However, I am getting a syntax in

Cells(r, c).Formula = "=indirect(""" & Right(Cells(r, c).Formula,
Len(Cells(r, c).Formula) - 1) & """)"

which I copied and pasted. Any ideas?



"Ian" wrote:

Sub change_formula()
For c = 1 To 5
For r = 1 To 10
Cells(r, c).Formula = "=indirect(""" & Right(Cells(r, c).Formula,
Len(Cells(r, c).Formula) - 1) & """)"
Next r
Next c
End Sub

--
Ian
--
"Ronbo" wrote in message
...
I need to change a lot of formulas to "Indirect".

Example, A1, =B1 - Need to change it to =Indirect("B1")

I have been trying find and replace;

Find =, Replace XIndirect("
This produces XIndirect("B1

How can I Find the end of formula and add, ")

Any ideas on how to add to the end of the formula or any other way of
changing a formula to indirect would be appreciated.

The text has been wordwrapped and those 2 lines should all be on one line.

--
Ian
--
"Ronbo" wrote in message
...
Ian -

Thanks a lot, that is exactly what I need. However, I am getting a syntax
in

Cells(r, c).Formula = "=indirect(""" & Right(Cells(r, c).Formula,
Len(Cells(r, c).Formula) - 1) & """)"

which I copied and pasted. Any ideas?



"Ian" wrote:

Sub change_formula()
For c = 1 To 5
For r = 1 To 10
Cells(r, c).Formula = "=indirect(""" & Right(Cells(r, c).Formula,
Len(Cells(r, c).Formula) - 1) & """)"
Next r
Next c
End Sub

--
Ian
--
"Ronbo" wrote in message
...
I need to change a lot of formulas to "Indirect".

Example, A1, =B1 - Need to change it to =Indirect("B1")

I have been trying find and replace;

Find =, Replace XIndirect("
This produces XIndirect("B1

How can I Find the end of formula and add, ")

Any ideas on how to add to the end of the formula or any other way of
changing a formula to indirect would be appreciated.