| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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... |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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... |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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... |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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... |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro-indirect cell | Excel Discussion (Misc queries) | |||
| 2Q:Indirect worksheet selection in macro, Calc off for specific sh | Excel Discussion (Misc queries) | |||
| Using INDIRECT in a macro/vba | Excel Discussion (Misc queries) | |||
| Macro that sort on clnm populated by indirect | Excel Discussion (Misc queries) | |||
| Using Indirect & Creating a worksheet Macro | Excel Worksheet Functions | |||
Linear Mode
