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

#### bananius

##### New Member
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!

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
This code will work

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

Code:
``````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``````

This code will work

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

Code:
``````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

Last edited:
You have cells containing spaces or formula below AL41

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.

You have cells containing spaces or formula below AL41

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

Replies
4
Views
251
Replies
13
Views
2K
Replies
4
Views
1K
Replies
12
Views
330
Replies
4
Views
593

1,203,378
Messages
6,055,087
Members
444,761
Latest member
lneuberger

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back