Looping WORKDAY()

kneb7900

New Member
Joined
Nov 29, 2011
Messages
12
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
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:p></o:p>
Here is what I have so far:
<o:p></o:p>
CODE:
<o:p></o:p>
Sub DeliveryDate()
<o:p></o:p>
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:p></o:p>
Do
ActiveCell.FormulaR1C1 = "=Workday(RC[-2],RC[-1], Holidays)" ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub
<o:p></o:p>
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:p></o:p>
Thanks in advance!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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!
 
Upvote 0
A bunch of issues:

First,
Code:
Dim HD1, HDE, Holidays As Range
only declares Holidays as Range.
You need to write either:
Code:
Dim HD1 as Range, HDE as Range, Holidays As Range
or
Code:
Dim HD1 as Range
Dim HDE as Range
Dim Holidays As Range

Second,
It should be:
Code:
Set HDE = HD1.End(x[B][COLOR=red]l[/COLOR][/B]Down)
not
Code:
Set HDE = HD1.End(x[COLOR=red][B]1[/B][/COLOR]Down)
(note it is the letter "l", not the number "1").

Third,
This:
Code:
ActiveCell.FormulaR1C1 = "=Workday(RC[-2],RC[-1], Holidays)" ActiveCell.Offset(1, 0).Select
should be split into two lines like this:
Code:
ActiveCell.FormulaR1C1 = "=Workday(RC[-2],RC[-1], Holidays)"
ActiveCell.Offset(1, 0).Select
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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').
 
Upvote 0
#(explitive)#!!!!

I mixed R1C1 with A1...

let's see, that would then be..

ActiveCell.FormulaR1C1 = "=Workday(RC[-2],RC[-1], 'Holiday List'!R1C1:R20C1)"
 
Upvote 0
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?
 
Upvote 0
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)"
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top