Getting previous 10 workdays (excluding weekends) from Today (vba)

[xs4amit]

I am working on a sheet which requires previous 10 working days from today:

Something like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]10-Dec[/TD]
[TD]11-Dec[/TD]
[TD]12-Dec[/TD]
[TD]13-Dec[/TD]
[TD]14-Dec[/TD]
[TD]17-Dec[/TD]
[TD]18-Dec[/TD]
[TD]19-Dec[/TD]
[TD]20-Dec[/TD]
[TD]21-Dec[/TD]
[/TR]
</tbody>[/TABLE]

i am scratching my head from last 3 days with all kind of wiered loops but nothing is working. Can anyone help me with a nice VBA code.

 

[xs4amit]

Thanks Guys,

I have cracked it finally, here it goes:

With WS1




lstwday = Format(Date, "dddd")
Select Case lstwday
Case Is = "Sunday"


.Cells(2, 27) = Date - 2
Case Is = "Saturday"
.Cells(2, 27) = Date - 1
Case Else
.Cells(2, 27) = Date
End Select


Arr1 = .Range("R4:AA4")
Arr2 = .Range("Z2:AA2")
u = 0
For r = 10 To 1 Step -1


strday = Format(Arr2(1, 2) - u, "dddd")
If strday = "Sunday" Then
GoTo ibiza






ibiza:
u = u + 2
Arr1(1, r) = Arr2(1, 2) - u
u = u + 1
Else
Arr1(1, r) = Arr2(1, 2) - u
u = u + 1


End If
Next


.Range("R4:AA4") = Arr1

Let me know if someone can refine it. Improvement ideas are most welcome.

 

[Eric W]

How about:

Sub testdate()
Dim Arr1(1 To 1, 1 To 10), i As Long

    For i = -9 To 0
        Arr1(1, i + 10) = WorksheetFunction.WorkDay(Date, i)
    Next i
    ActiveSheet.Range("R4:AA4") = Arr1
End Sub

The worksheet function WORKDAY does exactly what you want, and you can call it from VBA.

 

[Eric W]

You can get it even shorter if you want with:

Sub testdate2()

    Range("R4:AA4").Formula = "=WORKDAY(TODAY(),1-COLUMNS(R4:$AA4))"
    Range("R4:AA4").Value = Range("R4:AA4").Value
    
End Sub

Depending on your ultimate goal, you can just use worksheet formulas too.

 

[xs4amit]

Fantastic Eric,

This is phenomenal how you reduced my 10 lines to 3 lines. Thanks

How about:

Sub testdate()
Dim Arr1(1 To 1, 1 To 10), i As Long

    For i = -9 To 0
        Arr1(1, i + 10) = WorksheetFunction.WorkDay(Date, i)
    Next i
    ActiveSheet.Range("R4:AA4") = Arr1
End Sub

The worksheet function WORKDAY does exactly what you want, and you can call it from VBA.