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