Hello

I have have this formula:

Sub HoldingShelf()
ActiveCell.FormulaR1C1 = "HOLDING SHELF"
ActiveCell.Offset(0, 1).FormulaR1C1 = "=NOW()"
ActiveCell.Offset(0, 2).Interior.ColorIndex = 15
ActiveCell.Offset(0, 3).FormulaR1C1 =
"=MAX(0,NETWORKDAYS(TODAY(),(Edate(E45,6))))"
End Sub

it works fine if i enter the cell refferance self but i want to use it in a
macro. so i need the Edate part to be offset so where ever i use the macro it
will use the offset cell is this possible?

I tried using =MAX(0,NETWORKDAYS(TODAY(),(Edate(R[-6];C[0],6))))" but it
gives me a range of cells and i just need a single cell.

Any help would be much appreciated.

Thanks

You should enter the correct formula in the desired cell and then switch to
R1C1 format (on the Tools, Options, General tab) to get the right formula in
that reference style.

--
Jim
"Leon" wrote in message
...
| Hello
|
| I have have this formula:
|
| Sub HoldingShelf()
| ActiveCell.FormulaR1C1 = "HOLDING SHELF"
| ActiveCell.Offset(0, 1).FormulaR1C1 = "=NOW()"
| ActiveCell.Offset(0, 2).Interior.ColorIndex = 15
| ActiveCell.Offset(0, 3).FormulaR1C1 =
| "=MAX(0,NETWORKDAYS(TODAY(),(Edate(E45,6))))"
| End Sub
|
| it works fine if i enter the cell refferance self but i want to use it in
a
| macro. so i need the Edate part to be offset so where ever i use the macro
it
| will use the offset cell is this possible?
|
| I tried using =MAX(0,NETWORKDAYS(TODAY(),(Edate(R[-6];C[0],6))))" but it
| gives me a range of cells and i just need a single cell.
|
| Any help would be much appreciated.
|
| Thanks
|

You didn't use the correct syntax for the R1C1 reference style:

ActiveCell.Offset(0, 3).FormulaR1C1 _
= "=MAX(0,NETWORKDAYS(TODAY(),(Edate(R[-6]C,6))))"

r[-6]c[0] is the same as plain old r[-6]c.



Leon wrote:

Hello

I have have this formula:

Sub HoldingShelf()
ActiveCell.FormulaR1C1 = "HOLDING SHELF"
ActiveCell.Offset(0, 1).FormulaR1C1 = "=NOW()"
ActiveCell.Offset(0, 2).Interior.ColorIndex = 15
ActiveCell.Offset(0, 3).FormulaR1C1 =
"=MAX(0,NETWORKDAYS(TODAY(),(Edate(E45,6))))"
End Sub

it works fine if i enter the cell refferance self but i want to use it in a
macro. so i need the Edate part to be offset so where ever i use the macro it
will use the offset cell is this possible?

I tried using =MAX(0,NETWORKDAYS(TODAY(),(Edate(R[-6];C[0],6))))" but it
gives me a range of cells and i just need a single cell.

Any help would be much appreciated.

Thanks

--

Dave Peterson

Wow you answered fast, Oh that's what i did thank you, your a star it works
fine now.

"Dave Peterson" wrote:

You didn't use the correct syntax for the R1C1 reference style:

ActiveCell.Offset(0, 3).FormulaR1C1 _
= "=MAX(0,NETWORKDAYS(TODAY(),(Edate(R[-6]C,6))))"

r[-6]c[0] is the same as plain old r[-6]c.



Leon wrote:

Hello

I have have this formula:

Sub HoldingShelf()
ActiveCell.FormulaR1C1 = "HOLDING SHELF"
ActiveCell.Offset(0, 1).FormulaR1C1 = "=NOW()"
ActiveCell.Offset(0, 2).Interior.ColorIndex = 15
ActiveCell.Offset(0, 3).FormulaR1C1 =
"=MAX(0,NETWORKDAYS(TODAY(),(Edate(E45,6))))"
End Sub

it works fine if i enter the cell refferance self but i want to use it in a
macro. so i need the Edate part to be offset so where ever i use the macro it
will use the offset cell is this possible?

I tried using =MAX(0,NETWORKDAYS(TODAY(),(Edate(R[-6];C[0],6))))" but it
gives me a range of cells and i just need a single cell.

Any help would be much appreciated.

Thanks

--

Dave Peterson