I am trying to calculate the number of business days between dates in two fields, excluding company holidays, which I have entered on a separate worksheet in a named range.

My code works fine if I don’t try to exclude the holidays, but as soon as I add the range parameter, no value is returned.

This works fine:
ActiveCell.FormulaR1C1 = "=NETWORKDAYS([@[AdjAPSRecvdDt]],[@[APS_ACK_DT]])"

However, this does not:
ActiveCell.FormulaR1C1 = "=NETWORKDAYS([@[AdjAPSRecvdDt]],[@[APS_ACK_DT]],Holidays!HRange)"

The code will run, but the fields in the spreadsheet just have “#VALUE!”

I have used the formula evaluator, and everything evaluates fine, until it tries to calculate the final value.

This is what it looks like in the Evaluate Formula box before receiving the “#VALUE!” error:

NETWORKDAYS($K$2,$N$2,"Holidays!$A$2:$A$28")

Any help would be greatly appreciated. Thank you.