Looping WORKDAY()

[kneb7900]

SITUATION:
Starting with a table where each row contains information about a different item to be shipped, I want to add a column that calculates the expected delivery date. The delivery date is calculated using the workday function =WORKDAY(start_date, days, [holidays]).
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Start_date (shipment day) and days (# of workdays in transit) are already in the table. Holidays are listed in a column on another sheet called Holiday List. I have tried a few solutions and the closest I can get is getting the #NAME? error.
<o></o>
Here is what I have so far:
<o></o>
CODE:
<o></o>
Sub DeliveryDate()
<o></o>
Dim HD1, HDE, Holidays As Range
Set HD1 = Worksheets("Holiday List").Cells(4, 1)
Set HDE = HD1.End(x1Down)
Set Holidays = Range(HD1, HDE)
<o></o>
Do
ActiveCell.FormulaR1C1 = "=Workday(RC[-2],RC[-1], Holidays)" ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub
<o></o>
This version of the code bugs out at Set HDE = HD1.End(x1Down). My other problem though, is that the workday function ends up reading Holidays as ‘Holiday List’!‘A4’:‘A11’ (The list ends at A11) and should be read ‘Holiday List’!A4:A11.
<o></o>
Thanks in advance!

 

[kneb7900]

I am using Excel 2007, so I went to Formulas>Defined Names>Name Manager and created a new name.

This is what I have:
Name: Holidays
Refers to: =OFFSET('Holiday List'!$A$4, 0, 0, COUNT('Holiday List'!$A$4:$A$200), 3)

Now when I use
ActiveCell.FormulaR1C1 = "=Workday(RC[-2],RC[-1], Holidays)"
I get the #Name? error again.

I don't know that that list is dynamic either though... I want users to be able to add new holidays to the list without messing this up.

 

[Jonmo1]

Make sure the Scope of the Name is "Workbook" and not "Holiday List"

 

[kneb7900]

I can't get the scope of the range to change. It's strange, because I have another named range in the same sheet (with a different name) which was automatically set to have workbook scope.

Any ideas?

 

[Jonmo1]

I think you have to delete, and recreate the Name.
Make sure it's scope is workbook at the time you create it.

 

[kneb7900]

Yes! It works! I had one more error which was the 3 in the dynamic range should have been a 1.

Thank you for your time! I really appreciate it!

For anyone who checks out this thread later, here is an overview:

CODE:

Sub DeliveryDate()
Do
ActiveCell.FormulaR1C1 = "=Workday(RC[-2],RC[-1], Holidays)"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
End Sub

Where Holidays is a dynamic name as follows:
Name: Holidays
Scope: Workbook
Refers to: =OFFSET('Holiday List'!$A$4,0,0,COUNT('Holiday List'!$A$4:$A$200),1)

Holiday List is a worksheet with a column of dates beginning at A4.

This macro is used on the column of a worksheet where the column to the immediate left contains the number of workdays the items are in transit. The second column to the left contains the shipment date.

 

[Jonmo1]

Glad you got it sorted.

FYI, the 3 in the range was just making it a 3 Column Range.
And a 3D range (A1:C10) is not a problem for the Holiday range in the Workday function.
It's not necessary, we usually list dates for this purpose in a single column.
But it won't cause any error having a 3D range.
And it will actually work..