About Us

KevHardy

Thanks Rick :-)

"Rick Rothstein" wrote:

No, assuming G2 and G3 hold real dates, you can leave Start and Finish
declared as Dates. I modified the code just a wee bit (changed Z variable to
Rw, to signify "row", and initialized it before the loop and move its
updating within the For..Next loop)... Rw is the row the dates are being
outputted to and now you can specify the starting row for the output by
changing the 1 in the Rw=1 statement to whatever starting row number you
want to start outputting your dates to. Here is the modified macro
incorporating the G2/G3 change as well as the Z/Rw change...

Sub PopulateWorkdays()
Dim X As Long, Rw As Long, Start As Date, Finish As Date, H() As String
H = Split(Join(WorksheetFunction.Transpose(Range("Holi days"))))
Start = Range("G2")
Finish = Range("G3")
Rw = 1
For X = Start To Finish
If Weekday(X, vbMonday) < 6 And UBound(Filter(H, X)) < 0 Then
Cells(Rw, "A").Value = X
Rw = Rw + 1
End If
Next
End Sub

--
Rick (MVP - Excel)

"KevHardy" wrote in message
...
Hi Rick,
How would I change your code to allow the user to enter a start date and
end
date? i.e. the Start and Finish would be from cell contents (G2 and G3
perhaps) rather than DateSerial().
I've tried changing this myself but don't really know what I'm doing :-)

I'm presuming the Dim will be different (Range rather than Date?)

"Rick Rothstein" wrote:

Here is another approach for you to consider (just set the start and
finish dates and the holiday named range as needed)...

Sub PopulateWorkdays()
Dim X As Long, Z As Long, Start As Date, Finish As Date, H() As String
H = Split(Join(WorksheetFunction.Transpose(Range("Holi days"))))
Start = DateSerial(2010, 1, 1)
Finish = DateSerial(2010, 12, 31)
For X = Start To Finish
If Weekday(X, vbMonday) < 6 And UBound(Filter(H, X)) < 0 Then
Z = Z + 1
Cells(Z, "A").Value = X
End If
Next
End Sub

--
Rick (MVP - Excel)

"KevHardy" wrote in message
...
I have used the workday function to populate column A with just workdays
(also used a named list of holidays to exclude).
I was wondering if anyone had a bit of vba to automate this process?
.

.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Workday function question	Spike	Excel Worksheet Functions	2	February 15th 10 02:52 PM
WORKDAY	Daniel Q.	Excel Worksheet Functions	6	August 13th 08 03:20 PM
workday formula question	notanexcelguru	Excel Discussion (Misc queries)	3	May 9th 08 03:33 PM
WORKDAY() and probably more	Epinn	New Users to Excel	19	September 24th 06 01:19 PM
Workday Function Question	PA	Excel Worksheet Functions	2	July 16th 05 03:08 AM

Menu

About Us