Networkdays formula in macro

[orquidea]

Hi:
I want to write the statement ActiveCell = "=NETWORKDAYS(I2,O2)"
replacing I2 with ActiveCell.Offset(0,-9) and O2 with
(ActiveCell.Offset(0,-3). My string below gives me the error #NAME?

ActiveCell =
"=NETWORKDAYS((ActiveCell.Offset(0,-9)),(ActiveCell.Offset(0,-3))"


Could anyone help me please.

Thanks,
Orquidea

 

[Dave Peterson]

maybe...

ActiveCell.formula = "=NETWORKDAYS(" & ActiveCell.Offset(0, -9).Address(0, 0) _
& "," & ActiveCell.Offset(0, -3).Address(0, 0) & ")"

 

[Mike H]

Hi,

I'm not sure what values you intend to put in these 2 offset cells but try
this

ActiveCell = "=NETWORKDAYS(" & ActiveCell.Offset(0, -9).Value & "," &
ActiveCell.Offset(0, -3).Value & ")"

The answer has line-wrapped

Mike

 

[orquidea]

Thanks Mike:

I tried your string but it gives me result 0.

I2 (ActiveCell.Offset(0,-9) = 05/30/08 and O2(ActiveCell.Offset(0,-3)=
06/02/08. The result I should get is 2.

Orquidea

 

[Mike H]

Hi,

The formula I gave you works fine if the offset cells contain cell
references but you never said what you were using. This works for dates in
those cells. Note the additional quotes.

ActiveCell = "=NETWORKDAYS(""" & ActiveCell.Offset(0, -9) & """,""" &
ActiveCell.Offset(0, -3) & """)"

Mike

 

[orquidea]

My mistake, I assumed that NETWORKDAYS just workes with dates, I guess not.
Thanks, It works now.

 

[Mike H]

Hi,

Networkdays works with dates directly or with cell references that refer to
dates but if using dates directly extra quotes are needed. Thanks for the
feedback.

Mike