-1

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!

anci
  • 15
  • 1
  • 4

1 Answers1

0

A worksheet formula to get the row number of the last populated cell in a column is

=match("zzzzz",A:A,1)

You can use this in the offset function to fence in the range.

teylyn
  • 34,374
  • 4
  • 53
  • 73