+ Reply to Thread
Results 1 to 5 of 5

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

Hybrid View

  1. #1
    Registered User
    Join Date
    07-29-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    16

    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!

  2. #2
    Registered User
    Join Date
    07-29-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    16

    Re: How do I create a macro from a SUMPRODUCT formula for a range whose number of rows cha

    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!

  3. #3
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: How do I create a macro from a SUMPRODUCT formula for a range whose number of rows cha

    hi bananius,

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

  4. #4
    Registered User
    Join Date
    07-29-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    16

    Re: How do I create a macro from a SUMPRODUCT formula for a range whose number of rows cha

    Quote Originally Posted by watersev View Post
    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!!!

  5. #5
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: How do I create a macro from a SUMPRODUCT formula for a range whose number of rows cha

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro - Create / Copy / Paste Formula every 5 rows for varying range
    By Jill Pleau in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-29-2014, 12:31 PM
  2. Replies: 7
    Last Post: 04-03-2014, 07:10 PM
  3. Can't get over certain number of rows for SUMPRODUCT formula
    By Matthew_TO in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-23-2013, 03:48 PM
  4. Replies: 1
    Last Post: 01-17-2013, 10:44 AM
  5. Create Macro To Copy Moving Range of Rows
    By bselwin in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-01-2007, 09:04 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1