WORKDAY question
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? |
WORKDAY question
You can use code similar to the following. Sub AAA() Dim R As Range Dim StartDate As Date Dim NumDays As Long Dim N As Long Dim Holidays() As Variant Holidays = Range("E1:E10") '<<< CHANGE StartDate = DateSerial(2010, 1, 1) '<<< CHANGE NumDays = 30 '<<< CHANGE Set R = Range("A1") '<<< CHANGE On Error Resume Next For N = 0 To NumDays If Weekday(StartDate + N, vbMonday) <= 5 Then If IsError(Application.Match(StartDate + N, Holidays, 0)) Then R.Value = StartDate + N Set R = R(2, 1) End If End If Next N End Sub Change the lines marked with <<< to your values. Holidays is the range that contains the holidays to exclude, StartDate is the first date at which the list is to begin. NumDays is the number of weekdays to include in the list, and R is the cell in which the list is to begin. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Sun, 21 Feb 2010 13:08:01 -0800, KevHardy wrote: 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? |
WORKDAY question
Hi
Change Sht to the sheet you want and manually populate your holidays in the range E1 - E10 then run the code and you get your list of workdays in col A. They will start from the date after the variable MyDate Sub MyMacro() Set Sht = Sheets("Sheet1") Dim MyDate As Date MyDate = #12/31/2009# For x = 1 To 365 Do Sht.Cells(x, 1) = WorksheetFunction.WorkDay(MyDate, 1) If WorksheetFunction.CountIf(Sht.Range("E1:E10"), Sht.Cells(x, 1)) 0 Then MyDate = MyDate + 1 End If Loop Until WorksheetFunction.CountIf(Sht.Range("E1:E10"), Sht.Cells(x, 1)) = 0 MyDate = Sht.Cells(x, 1).Value Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "KevHardy" wrote: 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? |
WORKDAY question
Just spotted a bug in that try this instead
Sub MyMacro() Set Sht = Sheets("Sheet1") Dim MyDate As Date MyDate = #12/31/2009# For x = 1 To 365 Do Sht.Cells(x, 1) = WorksheetFunction.WorkDay(MyDate, 1) If WorksheetFunction.CountIf(Sht.Range("E1:E10"), Sht.Cells(x, 1)) 0 Then Sht.Cells(x, 1) = WorksheetFunction.WorkDay(Sht.Cells(x, 1), 1) End If Loop Until WorksheetFunction.CountIf(Sht.Range("E1:E10"), Sht.Cells(x, 1)) = 0 MyDate = Sht.Cells(x, 1).Value Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "KevHardy" wrote: 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? |
WORKDAY question
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? |
WORKDAY question
Thanks Mike, Chip and Rick :-)
It's interesting to see three versions of tackling the same task - and the notes made it much easier to see what's going on. Thanks a million :-) "KevHardy" wrote: 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? |
WORKDAY question
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? . |
WORKDAY question
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? . |
WORKDAY question
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? . . |
All times are GMT +1. The time now is 01:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com