an array to display weekdays only

[yu]

I am trying to create an array which will display the dates for all the days of year in a row in an excel sheet , however i want it to skip weekends and bank holidays and in the place of the weekends I was to display the work WEEKEND.

eg.
05/02/2001
06/02/2001
07/02/2001
08/02/2001
09/02/2001
Weekend
12/02/2001
etc....


Can anyone give me a few pointers on where to start please????

 

[Ruairi]

Private Sub FillDates()
    Dim DateToPrint As Date
    Dim StartDate As Date
    Dim Row As Long
    Dim CellName As String
    
    StartDate = Date
    DateToPrint = Date
    Row = 1
    
    While DateToPrint < DateAdd(&quot;yyyy&quot;, 1, StartDate)
    CellName = &quot;A&quot; &amp; Trim(Str(Row))
    If WeekDay(DateToPrint) = vbSaturday Then
        ActiveSheet.Range(CellName).Value = &quot;WEEKEND&quot;
    ElseIf WeekDay(DateToPrint) <> vbSunday Then
        ActiveSheet.Range(CellName).Value = Format(DateToPrint, &quot;mm/dd/yyyy&quot;)
    End If
        
    If WeekDay(DateToPrint) <> vbSunday Then Row = Row + 1
    DateToPrint = DateAdd(&quot;d&quot;, 1, DateToPrint)
    Wend
End Sub

Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[BlackKnight]

Private Sub cmdCalendar_Click()
'If you're planning to use matrix for more than just
'temporay holder of values to be inserted into spreadsheet
'then change to an array of 365 elements.
ReDim Calendar(1 To 12, 1 To 365) As String
Dim ADate As String
Dim DOW As Integer
Dim Mth As Byte, NumDays As Byte, D As Byte

For Mth = 1 To 12
NumDays = DaysinMonth(Mth)
For D = 1 To NumDays
ADate = Format$(Mth, "00&quot & "/" & Format$(D, "00&quot & "/" & txtYear
'Check for bank holidays. Assuming if bank holiday falls on
'a weekend then display 'Weekend'.
If IsBankHoliday(ADate) Then
ADate = "Holiday"
Else
DOW = Weekday(ADate, vbUseSystemDayOfWeek)
If DOW = vbSaturday Or DOW = vbSunday Then
ADate = "Weekend"
End If
End If
Calendar(Mth, D) = ADate
Next D
Next Mth
End Sub

Private Function DaysinMonth(ByVal Mth As Byte) As Byte
Dim NumDays As Byte

Select Case Mth
Case 1, 3, 5, 7, 8, 10, 12
NumDays = 31
Case 2
'Check for leap year.
If CInt(txtYear.Text) Mod 4 = 0 Then
NumDays = 29
Else
NumDays = 28
End If
Case 4, 6, 9, 11
NumDays = 30
Case Else
NumDays = -1
End Select
DaysinMonth = NumDays
End Function

Private Function IsBankHoliday(ByVal ADate As String) As Boolean
Dim Result As Boolean
Dim i As Integer

Result = False
'Substitute your storage method of bank holidays
For i = 0 To lstHolidays.ListCount - 1
If lstHolidays.List(i) = ADate Then
Result = True
Exit For
End If
Next i
BankHoliday = Result