How do I create a macro from a SUMPRODUCT formula for a range whose number of rows changes?

[bananius]

I have a SUMPRODUCT formula which can be written as either:
=SUMPRODUCT(0+(COUNTIF(OFFSET(AL2:AT2,ROW(AL2:AT?)-MIN(ROW(AL2:AT?)),,),"VAC")<5))
OR
=SUMPRODUCT(--(MMULT(--(AL2:AT?="Vac"),{1;1;1;1;1;1;1;1;1})<5))
However, I have to apply this formula to different sheets. The column range always stays the same (AL:AT) from sheet to sheet, but the number of rows changes per sheet. I'm starting the column range at AL2 to exclude the header.
I want to create a macro for the formula so that I can assign it to a command button so that it will count how many rows in the AL:AT range have less than five instances of the string "VAC" in it. And so the count pops up in a message box when you press the command button.
If anyone can provide any help that would be wonderful. Thank you in advance!

 

[CharlesChuckieCharles]

This code will work

Although it uses a loop Calculated from the activesheet and does a CountIf across the columns

Sub VacAlert()
Dim Boo As Variant
Dim MyDepth As Long
Dim doo As Long

    MyDepth = ActiveSheet.Cells(Rows.Count, "AL").End(xlUp).Row
    
    doo = 0
    For x = 2 To MyDepth
    
        Boo = Application.WorksheetFunction.CountIf(Range("AL" & x & ":AT" & x), "VAC")
        If Boo < 5 Then
          doo = doo + 1
        End If
    
    Next x
    
    MsgBox "Hey theres " & doo
End Sub

 

[bananius]

This code will work

Although it uses a loop Calculated from the activesheet and does a CountIf across the columns

Sub VacAlert()
Dim Boo As Variant
Dim MyDepth As Long
Dim doo As Long

    MyDepth = ActiveSheet.Cells(Rows.Count, "AL").End(xlUp).Row
    
    doo = 0
    For x = 2 To MyDepth
    
        Boo = Application.WorksheetFunction.CountIf(Range("AL" & x & ":AT" & x), "VAC")
        If Boo < 5 Then
          doo = doo + 1
        End If
    
    Next x
    
    MsgBox "Hey theres " & doo
End Sub

Thank you for your response CharlesChuckieCharles! However, when I put in the code it gave me a result of 77 rows when there are only 41! When I just did the MyDepth calculation it gave me 85 rows so I think that's where the problem lies. I also tried to do the ActiveSheet.Cells(Rows.Count... function but it was not returning the right results...

This is what the test range looks like which is from columns AB:AL

 

[CharlesChuckieCharles]

You have cells containing spaces or formula below AL41

 

[bananius]

You have cells containing spaces or formula below AL41

Correction! I just cleared the cells below and it worked!

Thank you, there must have been spaces there.

 

[bananius]

You have cells containing spaces or formula below AL41

Thank you so much @CharlesChuckieCharles! This was so helpful!!!