indirect macro
 

Is there a way to change cells that currently read
='week 1'!$a$17
to read
=indirect("'week1'!$A$17")
I have a few thousand cells to change each one referncing a different
cell...for instance Cell A18, Cell A19 etc...

indirect macro
 

For Each cell In selection
cell.Formula = "=INDIRECT(" & right(cell.formula,Len(cell.Formula)-1) &
")"
Next cell

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ladyhawk" wrote in message
...
Is there a way to change cells that currently read
='week 1'!$a$17
to read
=indirect("'week1'!$A$17")
I have a few thousand cells to change each one referncing a different
cell...for instance Cell A18, Cell A19 etc...

indirect macro
 

See one response to your previous post in m.p.e.worksheet.functions

In article ,
"ladyhawk" wrote:

Is there a way to change cells that currently read
='week 1'!$a$17
to read
=indirect("'week1'!$A$17")
I have a few thousand cells to change each one referncing a different
cell...for instance Cell A18, Cell A19 etc...

indirect macro
 

I am getting a syntax error when I attempted to run this macro...any ideas?

"Bob Phillips" wrote:

For Each cell In selection
cell.Formula = "=INDIRECT(" & right(cell.formula,Len(cell.Formula)-1) &
")"
Next cell

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ladyhawk" wrote in message
...
Is there a way to change cells that currently read
='week 1'!$a$17
to read
=indirect("'week1'!$A$17")
I have a few thousand cells to change each one referncing a different
cell...for instance Cell A18, Cell A19 etc...

indirect macro
 

probably word wrap, because it works fine.

Sub test()
For Each cell In Selection
cell.Formula = "=INDIRECT(" & Right(cell.Formula, Len(cell.Formula) - 1)
& _
")"
Next cell
End Sub

--


Gary


"ladyhawk" wrote in message
...
I am getting a syntax error when I attempted to run this macro...any ideas?

"Bob Phillips" wrote:

For Each cell In selection
cell.Formula = "=INDIRECT(" & right(cell.formula,Len(cell.Formula)-1)
&
")"
Next cell

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ladyhawk" wrote in message
...
Is there a way to change cells that currently read
='week 1'!$a$17
to read
=indirect("'week1'!$A$17")
I have a few thousand cells to change each one referncing a different
cell...for instance Cell A18, Cell A19 etc...

indirect macro
 

OK I tried to run it again...
this is what the formula in the cell ends up being:
=INDIRECT(" & Right(cell.Formula, Len(cell.Formula) - 1) & ")

I must be doing something wrong.....please help

"Gary Keramidas" wrote:

probably word wrap, because it works fine.

Sub test()
For Each cell In Selection
cell.Formula = "=INDIRECT(" & Right(cell.Formula, Len(cell.Formula) - 1)
& _
")"
Next cell
End Sub

--


Gary


"ladyhawk" wrote in message
...
I am getting a syntax error when I attempted to run this macro...any ideas?

"Bob Phillips" wrote:

For Each cell In selection
cell.Formula = "=INDIRECT(" & right(cell.formula,Len(cell.Formula)-1)
&
")"
Next cell

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ladyhawk" wrote in message
...
Is there a way to change cells that currently read
='week 1'!$a$17
to read
=indirect("'week1'!$A$17")
I have a few thousand cells to change each one referncing a different
cell...for instance Cell A18, Cell A19 etc...