Macro MsgBox issue

[KaaNunL]

Hi Everyone,

I have quick question

How can i add MsgBox if there is no "fail" current week ?

Is it possible to help me to finish this macro ?

Thank you

Sub GetFilterForFails()
'
' GetFilterForFails Macro
'


'


Dim CurrentYear As Integer
    Dim previousweek As Integer
    Dim slaUK As String
    


    CurrentYear = DatePart("yyyy", Date)
    previousweek = DatePart("ww", Date) - 1
    slaUK = "SLA builder UK.xlsm"


    Sheets("SLA - Order delivery").Select
    Selection.AutoFilter
    ActiveSheet.ListObjects("tbl_SLA_OrderDelivery").Range.AutoFilter Field:=30, _
        Criteria1:=CurrentYear
    ActiveSheet.ListObjects("tbl_SLA_OrderDelivery").Range.AutoFilter Field:=29, _
        Criteria1:=previousweek
    ActiveSheet.ListObjects("tbl_SLA_OrderDelivery").Range.AutoFilter Field:=28, _
        Criteria1:="Fail"
        
End Sub

 

[gallen]

Does this do the job?

Sub GetFilterForFails()
'
' GetFilterForFails Macro
'
Dim CurrentYear As Integer
Dim previousweek As Integer
Dim slaUK As String
[COLOR=#ff0000]Dim lFails As Long[/COLOR]

CurrentYear = DatePart("yyyy", Date)
previousweek = DatePart("ww", Date) - 1
slaUK = "SLA builder UK.xlsm"

Sheets("SLA - Order delivery").Select
Selection.AutoFilter
ActiveSheet.ListObjects("tbl_SLA_OrderDelivery").Range.AutoFilter Field:=30, _
Criteria1:=CurrentYear
ActiveSheet.ListObjects("tbl_SLA_OrderDelivery").Range.AutoFilter Field:=29, _
Criteria1:=previousweek
ActiveSheet.ListObjects("tbl_SLA_OrderDelivery").Range.AutoFilter Field:=28, _
Criteria1:="Fail"

[COLOR=#ff0000]On Error Resume Next
lFails = ActiveSheet.ListObjects("tbl_SLA_OrderDelivery").DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Count
On Error GoTo 0

If lFails = 0 Then MsgBox "ERROR"[/COLOR]

End Sub

 

[KaaNunL]

Yes!! Thanks a lot mate