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!

 

[Joe4]

If you test a simple WORKDAY function in your workbook, does it work?
The reason why I ask is because the NAME# error usually denotes that you do not have the Analysis ToolPak enabled, so the WORKDAY function is not recognized. See: http://www.ehow.com/how_2238503_use-excel-workday-function.html

 

[kneb7900]

Yes, the WORKDAY function works fine in the actual workbook. I've been essentially repeating the process of this code manually and I'm sick of it, ha!

 

[Joe4]

A bunch of issues:

First,

Dim HD1, HDE, Holidays As Range

only declares Holidays as Range.
You need to write either:

Dim HD1 as Range, HDE as Range, Holidays As Range

or

Dim HD1 as Range
Dim HDE as Range
Dim Holidays As Range

Second,
It should be:

Set HDE = HD1.End(x[B][COLOR=red]l[/COLOR][/B]Down)

not

Set HDE = HD1.End(x[COLOR=red][B]1[/B][/COLOR]Down)

(note it is the letter "l", not the number "1").

Third,
This:

ActiveCell.FormulaR1C1 = "=Workday(RC[-2],RC[-1], Holidays)" ActiveCell.Offset(1, 0).Select

should be split into two lines like this:

ActiveCell.FormulaR1C1 = "=Workday(RC[-2],RC[-1], Holidays)"
ActiveCell.Offset(1, 0).Select

 

[kneb7900]

Thanks for those! Now everything works, except I still get the #NAME? error. As I mentioned earlier, I think this is because of the way the Holidays range is being read, since the WORKDAY() function works properly in the worksheet.

 

[Jonmo1]

In your original post you said..

Holidays are listed in a column on another sheet called Holiday List.

That means you have to reference the sheet and the column..
You can't just say "Holidays"

ActiveCell.FormulaR1C1 = "=Workday(RC[-2],RC[-1], 'Holiday List'!A1:A20)"

Your holidays are in the sheet named "Holiday List"
In range A1:A20

 

[kneb7900]

Here is what I get right now when I run the macro in G1:

<TABLE style="WIDTH: 211pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=279><COLGROUP><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 512" span=4 width=14><COL style="WIDTH: 12pt; mso-width-source: userset; mso-width-alt: 585" width=16><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2157" width=59><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 11pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17 width=14> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 11pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=14>

A​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 11pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=14>

B​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 11pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=14>

C​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 12pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=16>

D​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 66pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=88>

E​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 45pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=60>

F​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 44pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=59>

G​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=17 align=right>

1​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>

a​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>

b​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>

c​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

d​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>

2/7/2012​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>

5​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=middle>

#NAME?​

</TD></TR></TBODY></TABLE>

The formula bar then reads: =WORKDAY(F2,G2, 'Holiday List'!'A1':'A20').

I don't understand why it is converted to this form from the VBA code (i.e. 'A1':'A20').

 

[Jonmo1]

#(explitive)#!!!!

I mixed R1C1 with A1...

let's see, that would then be..

ActiveCell.FormulaR1C1 = "=Workday(RC[-2],RC[-1], 'Holiday List'!R1C1:R20C1)"

 

[kneb7900]

Awesome! Now, is there a way to use a dynamic named list instead of R1C1:R20C1? That's what I was trying to do with Holidays.

Also, how can you be sure that the new column is formatted to show date values?

 

[Jonmo1]

You have to create a named range in the sheet.
Highlight your range of Holiday dates
click Insert - Name - Define
Give it a name, Holidays

Then it's back the way it was
ActiveCell.FormulaR1C1 = "=Workday(RC[-2],RC[-1], Holidays)"