Edate Question

[Leon]

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

 

[Jim Rech]

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
| 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]

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.

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

 

[Leon]

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

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.

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