NetworkDays with Dynamic Holiday Range

[reberryjr]

I feel exceptionally dumb for not being able to figure this out, but here I am. I would like to make the holiday list a dynamic range so that when next year's holiday schedule is released by my company, I don't have to adjust the code; I can simply add the dates to the subject range. Here's what I've tried thus far"

'Calculates the days to decision.
mHLR = mV.Range("P" & Rows.Count).End(xlUp).Row

HolidayList = mV.Range("P2:P" & mHLR).Value

'Returns a #NAME error
'mP.Range("AH2:AH" & mPLR).Value = "=NETWORKDAYS(RC[-20],RC[-15],HolidayList)"

'Returns a #VALUE error
'mP.Range("AH2:AH" & mPLR).Value = "=NETWORKDAYS(RC[-20]:RC[-15],Variables!C[-18])"

'Works, but isn't dynamic.
mP.Range("AH2:AH" & mPLR).FormulaR1C1 = "=NETWORKDAYS(RC[-20],RC[-15],Variables!R2C16:R12C16)-1"

 

[Fluff]

Try

'Calculates the days to decision.
Dim HolidayList as Range
mHLR = mV.Range("P" & Rows.Count).End(xlUp).Row

Set HolidayList = mV.Range("P2:P" & mHLR)


mP.Range("AH2:AH" & mPLR).Value = "=NETWORKDAYS(RC[-20],RC[-15]," & HolidayList & ")"

 

[reberryjr]

Try

'Calculates the days to decision.
Dim HolidayList as Range
mHLR = mV.Range("P" & Rows.Count).End(xlUp).Row

Set HolidayList = mV.Range("P2:P" & mHLR)


mP.Range("AH2:AH" & mPLR).Value = "=NETWORKDAYS(RC[-20],RC[-15]," & HolidayList & ")"

@Fluff thank you for the prompt response. I'm utilizing your suggestion, but getting a Type Mismatch error.

 

[Fluff]

Ok, how about

mP.Range("AH2:AH" & mPLR).formular1c1= "=NETWORKDAYS(RC[-20],RC[-15]," & HolidayList.Address(, , xlR1C1) & ")"

 

[reberryjr]

Ok, how about

mP.Range("AH2:AH" & mPLR).formular1c1= "=NETWORKDAYS(RC[-20],RC[-15]," & HolidayList.Address(, , xlR1C1) & ")"

I copy and pasted your suggestion into the code. The formula it places leavels out the worksheet reference.

=NETWORKDAYS(N3,S3,$P$2:$P$12)

 

[Fluff]

In that case use

HolidayList.Address(, , xlR1C1, 1)

 

[reberryjr]

In that case use

HolidayList.Address(, , xlR1C1, 1)

Ok, that worked. If you would, help me understand what these sections mean:

I assume

HolidayList.Address

puts the range I set earlier in the formula. I have no idea what this is

 (, , xlR1C1, 1)

 

[Fluff]

Have a look here Range.Address property (Excel)