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

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!

I'm trying to use Evaluate, but to now avail. I think it's still reading the statement as text. I'm not sure where to go from here.
This is what I have so far (I'm clearly not a programmer). Any explanations would help as to why this may not be working.

Sub VACCOUNT()

Dim total As Integer
Dim Var As String

Set Range1 = Range("AL2:AT41")
Var = "VAC"

total = ActiveSheet.Evaluate("SUMPRODUCT(--(MMULT(--(Range1=Var),{1;1;1;1;1;1;1;1;1})<5))")

MsgBox total

End Sub

Thank you in advance!

hi bananius,

total = ActiveSheet.Evaluate("SUMPRODUCT(--(MMULT(--(" & Range1.Address & "=" & """VAC""" & "),{1;1;1;1;1;1;1;1;1})<5))")

Originally Posted by watersev

hi bananius,

total = ActiveSheet.Evaluate("SUMPRODUCT(--(MMULT(--(" & Range1.Address & "=" & """VAC""" & "),{1;1;1;1;1;1;1;1;1})<5))")

Thank you for your help watersev!!!! It worked! Do you always have to put ".Address" when referring to a range? Also why are there two sets of quotation marks around ""VAC""?

Thanks again!!!

Address is used to identify cells address by string not object.

That's how the VBA works with text strings in formulas.