Excel Forum

Code: Select all

Public tbl As ListObject
Function GetTableData(projRow)

projName = tbl.DataBodyRange.Cells(projRow, tbl.ListColumns("In Charge").Index)
projDesc = tbl.DataBodyRange.Cells(projRow, tbl.ListColumns("Model").Index)
projDesc1 = tbl.DataBodyRange.Cells(projRow, tbl.ListColumns("Item").Index)
projDeadline = tbl.DataBodyRange.Cells(projRow, tbl.ListColumns("Target").Index)


projFullMsg = projName & " | " & projDesc & " : " & projDesc1 & " | Dead Line : " & projDeadline

GetTableData = projFullMsg

End Function

Sub LoopTable()
Set tbl = ActiveSheet.ListObjects("Table1")


dateToday = Date


NumRows = tbl.DataBodyRange.Rows.Count


    CountDue = 0
    For i = 1 To NumRows
        projDeadline = tbl.DataBodyRange.Cells(i, tbl.ListColumns("Target").Index)
        projFinish = tbl.DataBodyRange.Cells(i, tbl.ListColumns("Status").Index)
        If projFinish <> "Y" And projDeadline < dateToday Then
        DueMsg = DueMsg & GetTableData(i) & Chr(13) & Chr(10)
        CountDue = CountDue + 1
        End If
    Next i
    
    LineNotify "Your Job Overdue!! : " & CountDue & " Items" & Chr(13) & Chr(10) & DueMsg
    
   

NumDayNoti = Range("I1")
    CountPreNoti = 0
    For i = 1 To NumRows
        projDeadline = tbl.DataBodyRange.Cells(i, tbl.ListColumns("Target").Index)
        projFinish = tbl.DataBodyRange.Cells(i, tbl.ListColumns("Status").Index)
        If projFinish <> "Y" And (projDeadline - NumDayNoti) <= dateToday And projDeadline >= dateToday Then
        PreNotiMsg = PreNotiMsg & GetTableData(i) & Chr(13) & Chr(10)
        CountPreNoti = CountPreNoti + 1
        End If
    Next i
    
    LineNotify "Close to deadline : " & CountPreNoti & " Items" & Chr(13) & Chr(10) & PreNotiMsg
    
End Sub