cortexnotion
Board Regular
- Joined
- Jan 22, 2020
- Messages
- 150
- Office Version
- 2013
- Platform
- Windows
Please ignore, I figured out I had a corrupt date value in the column
Evening All
I'm having a strange problem with Workday in VBA. If the date difference goes below -44 the error message "unable to get the WorkDay property of the WorksheetFunction class" pops up. I've experimented with putting all values above -45 and the correct results are pasted back to the sheet. Any ideas?
Thanks!
Evening All
I'm having a strange problem with Workday in VBA. If the date difference goes below -44 the error message "unable to get the WorkDay property of the WorksheetFunction class" pops up. I've experimented with putting all values above -45 and the correct results are pasted back to the sheet. Any ideas?
Thanks!
VBA Code:
Sub TrackerData()
Dim DataInAry As Variant, DataOutAry As Variant,Hols As Variant, LR1 As Long, a As Long, b As Long,
LR1 = ThisWorkbook.Sheets("ProjWork").Range("D" & Rows.Count).End(xlUp).Row
DataInAry = ThisWorkbook.Sheets("ProjWork").Range("A3:I" & LR1).Value
Hols = Array("01/01/2021", "02/04/2021")
ReDim DataOutAry(1 To LR1, 1 To 9)
b = 1
For a = LBound(DataInAry) To UBound(DataInAry)
If InStr(DataInAry(a, 1), "TCS") = 0 Then
DataOutAry(b, 1) = WorksheetFunction.WorkDay(DataInAry(a, 9), -10, Hols)
DataOutAry(b, 2) = WorksheetFunction.WorkDay(DataInAry(a, 9), -21, Hols)
DataOutAry(b, 3) = WorksheetFunction.WorkDay(DataInAry(a, 9), -41, Hols)
DataOutAry(b, 4) = WorksheetFunction.WorkDay(DataInAry(a, 9), -52, Hols)
DataOutAry(b, 5) = WorksheetFunction.WorkDay(DataInAry(a, 9), -67, Hols)
DataOutAry(b, 6) = WorksheetFunction.WorkDay(DataInAry(a, 9), -78, Hols)
DataOutAry(b, 7) = WorksheetFunction.WorkDay(DataInAry(a, 9), -81, Hols)
DataOutAry(b, 8) = WorksheetFunction.WorkDay(DataInAry(a, 9), -107, Hols)
DataOutAry(b, 9) = WorksheetFunction.WorkDay(DataInAry(a, 9), -122, Hols)
b = b + 1
End If
Next a
ThisWorkbook.Sheets("ProjWork").Range("N3:V3").Resize(UBound(DataOutAry)).Value = DataOutAry