100 formulas taking way too long to run

J

justvba

New Member
Joined
Jan 6, 2017
Messages
41
Hi I have an excel file that has the user answer 3 questions then runs a query against a database. That take a min and thats ok but then the data is dropped into a sheet and there are over 100 formulas that are entered via VBA in about 6 sheets they all look like this

=Sumifs('Raw Data'!$Y:$Y,'Raw Data'!$L:$L,$A11,'Raw Data'!$A:$A,C$3,'Raw Data'!$F:$F,"USA")

There are just columns that change and I add a column every now and then. But this part takes well over an hour or 2 to run and I don't know what to do to speed it up.

Can anyone help me??
 
Sort by date Sort by votes
Jeffrey Mahoney said:
As Michael posted, you can get the last row used in a column using VBA.

You can use dynamic named ranges in formulas and in VBA. Here are two examples:

A text based, single column list where the list begins in Row 2 below the header, named "Roles": =offset($G$1,1,0,match("zzzzzzzz",$G:$G)-row($G$1),1)

A number based, two column list where the list begins in row 3 below the header, named "ZipCodes": =offset($G$2,1,0,match(1e+300,$G:$G)-row($G$2),2)

These formulas used in a named range will always return the entire list even when you add or subtract entries within the list. You can reference the range in VBA like this:

Code: 
Sub UseNamedRange()  

  Dim RolesRng As Range
  Dim Cel As Range
    
  Set RolesRng = Sheets("Setup").Range("Roles")
  For Each Cel In RolesRng
    Debug.Print Cel.Value
  Next Cel
  
  
End Sub
Click to expand...
I am confused on how I can get that to work with the sumifs this is my code


Code: 
Sub Format_Code()


Dim x As Long
Dim ans As String
Dim Major As String

Application.ScreenUpdating = False
Worksheets("Code").Visible = True
Worksheets("Values DO NOT ULTER").Visible = True
Worksheets("Values DO NOT ULTER").Activate
    Range("c2").Select
''''''''''''''''''''''''''''''''''''''''''''''''''''CODE SHEET'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Start:
Worksheets("Values DO NOT ULTER").Activate
x = 0

    Do Until IsEmpty(ActiveCell)
    ans = Worksheets(Worksheets.Count).Range("A5").Value
   
    
     If InStr(ActiveCell, ans) Then
        Major = Left(ActiveCell.Value, InStr(ActiveCell.Value, "/") - 1)
        
         'MsgBox "Match" Testing to see it the 2 matched or not
        ElseIf ans = "" Then
            
        Else
            Const BASE_NAME As String = "Code "
            Dim sheet_name As String
            Dim i As Integer
            Dim num_text As Integer
            Dim new_num As Integer
            Dim max_num As Integer
            Dim new_sheet As Worksheet
                
            max_num = 0
            For i = 1 To Sheets.Count
                sheet_name = Sheets(i).Name
                If Left$(sheet_name, Len(BASE_NAME)) = BASE_NAME _
                Then
                    num_text = Mid$(sheet_name, Len(BASE_NAME) + 1)
                    new_num = Val(num_text)
                    If new_num > max_num Then max_num = new_num
                End If
            Next i
            
            Set new_sheet = Sheets.Add(after:=Sheets(Sheets.Count))
            new_sheet.Name = BASE_NAME & Format$(Val(max_num) + 1)
            new_sheet.Select
            
        End If
      If IsEmpty(ActiveCell) Then
            GoTo Start:
            'MsgBox ("Something went wrong Please rerun the query.")
        'Exit Sub
        
        ElseIf x = 0 Then
            x = 5
        Worksheets(Worksheets.Count).Activate
        Range("A1").Select
          ActiveCell.Offset(2, 1).value = "January"
          ActiveCell.Offset(0, 2).value = "Febuary"
          ActiveCell.Offset(0, 3).value = "March"
          ActiveCell.Offset(0, 4).value = "April"
          ActiveCell.Offset(0, 5).value = "May"
          ActiveCell.Offset(0, 6).value = "June"
          ActiveCell.Offset(0, 7).value = "July"
          ActiveCell.Offset(0, 8).value = "August"
          ActiveCell.Offset(0, 9).value = "September"
          ActiveCell.Offset(0, 10).value = "October"
          ActiveCell.Offset(0, 11).value = "November"
          ActiveCell.Offset(0, 12).value = "December"
        Range("A5").Activate
            
         
            With ActiveSheet
                ActiveCell = Major
                
                    ActiveCell.Range("b2:m3,b5:m6").Select
                    ActiveCell.Offset(3, 0).Range("A1").Activate
                    Selection.Style = "Currency"
                    ActiveCell.Offset(-1, 0).Range("A1:L1").Select
                    Selection.Style = "Percent"
                    Selection.NumberFormat = "0.00%"
               Range("A5").Activate
                ActiveCell.Offset(1, 0) = Year(Now()) & "ALL SALES"
                ActiveCell.Offset(1, 1) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-1]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,1)"
                ActiveCell.Offset(1, 2) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-1]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,2)"
                ActiveCell.Offset(1, 3) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-1]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,3)"
                ActiveCell.Offset(1, 4) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-1]C1,'Raw Data'!C1,'sheet1'!r2c3 ,'Raw Data'!C3,4)"
                ActiveCell.Offset(1, 5) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-1]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,5)"
                ActiveCell.Offset(1, 6) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-1]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,6)"
                ActiveCell.Offset(1, 7) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-1]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,7)"
                ActiveCell.Offset(1, 8) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-1]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,8)"
                ActiveCell.Offset(1, 9) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-1]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,9)"
                ActiveCell.Offset(1, 10) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-1]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,10)"
                ActiveCell.Offset(1, 11) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-1]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,11)"
                ActiveCell.Offset(1, 12) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-1]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,12)"
                
                ActiveCell.Offset(2, 0) = Year(Now()) & " NET SALES"
                ActiveCell.Offset(2, 1) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-2]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,1,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(2, 2) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-2]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,2,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(2, 3) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-2]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,3,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(2, 4) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-2]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,4,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(2, 5) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-2]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,5,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(2, 6) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-2]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,6,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(2, 7) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-2]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,7,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(2, 8) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-2]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,8,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(2, 9) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-2]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,9,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(2, 10) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-2]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,10,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(2, 11) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-2]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,11,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(2, 12) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-2]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,12,'Raw Data'!C6,""USA"")"
                
                ActiveCell.Offset(3, 0) = ("Gross Margin % ")
                ActiveCell.Offset(3, 1) = "=((R[-1]C)-(SUMIFS('Raw Data'!C26,'Raw Data'!C14,R[-3]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,1,'Raw Data'!C6,""USA"")))/R[-1]C"
                ActiveCell.Offset(3, 2) = "=((R[-1]C)-(SUMIFS('Raw Data'!C26,'Raw Data'!C14,R[-3]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,2,'Raw Data'!C6,""USA"")))/R[-1]C"
                ActiveCell.Offset(3, 3) = "=((R[-1]C)-(SUMIFS('Raw Data'!C26,'Raw Data'!C14,R[-3]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,3,'Raw Data'!C6,""USA"")))/R[-1]C"
                ActiveCell.Offset(3, 4) = "=((R[-1]C)-(SUMIFS('Raw Data'!C26,'Raw Data'!C14,R[-3]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,4,'Raw Data'!C6,""USA"")))/R[-1]C"
                ActiveCell.Offset(3, 5) = "=((R[-1]C)-(SUMIFS('Raw Data'!C26,'Raw Data'!C14,R[-3]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,5,'Raw Data'!C6,""USA"")))/R[-1]C"
                ActiveCell.Offset(3, 6) = "=((R[-1]C)-(SUMIFS('Raw Data'!C26,'Raw Data'!C14,R[-3]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,6,'Raw Data'!C6,""USA"")))/R[-1]C"
                ActiveCell.Offset(3, 7) = "=((R[-1]C)-(SUMIFS('Raw Data'!C26,'Raw Data'!C14,R[-3]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,7,'Raw Data'!C6,""USA"")))/R[-1]C"
                ActiveCell.Offset(3, 8) = "=((R[-1]C)-(SUMIFS('Raw Data'!C26,'Raw Data'!C14,R[-3]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,8,'Raw Data'!C6,""USA"")))/R[-1]C"
                ActiveCell.Offset(3, 9) = "=((R[-1]C)-(SUMIFS('Raw Data'!C26,'Raw Data'!C14,R[-3]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,9,'Raw Data'!C6,""USA"")))/R[-1]C"
                ActiveCell.Offset(3, 10) = "=((R[-1]C)-(SUMIFS('Raw Data'!C26,'Raw Data'!C14,R[-3]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,10,'Raw Data'!C6,""USA"")))/R[-1]C"
                ActiveCell.Offset(3, 11) = "=((R[-1]C)-(SUMIFS('Raw Data'!C26,'Raw Data'!C14,R[-3]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,11,'Raw Data'!C6,""USA"")))/R[-1]C"
                ActiveCell.Offset(3, 12) = "=((R[-1]C)-(SUMIFS('Raw Data'!C26,'Raw Data'!C14,R[-3]C1,'Raw Data'!C1,'sheet1'!r2c3,'Raw Data'!C3,12,'Raw Data'!C6,""USA"")))/R[-1]C"
                
                ActiveCell.Offset(4, 0) = (Year(Now()) - 1) & "  NET SALES"
                ActiveCell.Offset(4, 1) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-4]C1,'Raw Data'!C1,'sheet1'!r2c2,'Raw Data'!C3,1,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(4, 2) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-4]C1,'Raw Data'!C1,'sheet1'!r2c2,'Raw Data'!C3,2,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(4, 3) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-4]C1,'Raw Data'!C1,'sheet1'!r2c2,'Raw Data'!C3,3,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(4, 4) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-4]C1,'Raw Data'!C1,'sheet1'!r2c2,'Raw Data'!C3,4,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(4, 5) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-4]C1,'Raw Data'!C1,'sheet1'!r2c2,'Raw Data'!C3,5,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(4, 6) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-4]C1,'Raw Data'!C1,'sheet1'!r2c2,'Raw Data'!C3,6,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(4, 7) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-4]C1,'Raw Data'!C1,'sheet1'!r2c2,'Raw Data'!C3,7,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(4, 8) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-4]C1,'Raw Data'!C1,'sheet1'!r2c2,'Raw Data'!C3,8,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(4, 9) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-4]C1,'Raw Data'!C1,'sheet1'!r2c2,'Raw Data'!C3,9,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(4, 10) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-4]C1,'Raw Data'!C1,'sheet1'!r2c2,'Raw Data'!C3,10,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(4, 11) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-4]C1,'Raw Data'!C1,'sheet1'!r2c2,'Raw Data'!C3,11,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(4, 12) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-4]C1,'Raw Data'!C1,'sheet1'!r2c2,'Raw Data'!C3,12,'Raw Data'!C6,""USA"")"
                
                ActiveCell.Offset(5, 0) = (Year(Now()) - 2) & " NET SALES"
                ActiveCell.Offset(5, 1) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-5]C1,'Raw Data'!C1,(Year(Now()) - 2),'Raw Data'!C3,1,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(5, 2) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-5]C1,'Raw Data'!C1,(Year(Now()) - 2),'Raw Data'!C3,2,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(5, 3) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-5]C1,'Raw Data'!C1,(Year(Now()) - 2),'Raw Data'!C3,3,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(5, 4) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-5]C1,'Raw Data'!C1,(Year(Now()) - 2),'Raw Data'!C3,4,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(5, 5) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-5]C1,'Raw Data'!C1,(Year(Now()) - 2),'Raw Data'!C3,5,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(5, 6) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-5]C1,'Raw Data'!C1,(Year(Now()) - 2),'Raw Data'!C3,6,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(5, 7) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-5]C1,'Raw Data'!C1,(Year(Now()) - 2),'Raw Data'!C3,7,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(5, 8) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-5]C1,'Raw Data'!C1,(Year(Now()) - 2),'Raw Data'!C3,8,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(5, 9) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-5]C1,'Raw Data'!C1,(Year(Now()) - 2),'Raw Data'!C3,9,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(5, 10) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-5]C1,'Raw Data'!C1,(Year(Now()) - 2),'Raw Data'!C3,10,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(5, 11) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-5]C1,'Raw Data'!C1,(Year(Now()) - 2),'Raw Data'!C3,11,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(5, 12) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-5]C1,'Raw Data'!C1,(Year(Now()) - 2),'Raw Data'!C3,12,'Raw Data'!C6,""USA"")"
                
            End With
            ThisWorkbook.Sheets("Values DO NOT ULTER").Activate
        'ActiveCell.Offset(0, 1).Activate
        Else
            x = x + 7
            ActiveCell.Copy
          Worksheets(Worksheets.Count).Activate
          With ActiveSheet
                 .Cells(x, 1).PasteSpecial xlPasteValues
                
                    ActiveCell.Range("b2:m3,b5:m6").Select
                    ActiveCell.Offset(3, 0).Range("A1").Activate
                    Selection.Style = "Currency"
                    ActiveCell.Offset(-1, 0).Range("A1:L1").Select
                    Selection.Style = "Percent"
                    Selection.NumberFormat = "0.00%"
                    
                ActiveCell.Offset(-3, -1).Select
                ActiveCell.Offset(1, 0) = Year(Now()) & " ALL SALES"
                ActiveCell.Offset(1, 1) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-1]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,1)"
                ActiveCell.Offset(1, 2) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-1]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,2)"
                ActiveCell.Offset(1, 3) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-1]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,3)"
                ActiveCell.Offset(1, 4) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-1]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,4)"
                ActiveCell.Offset(1, 5) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-1]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,5)"
                ActiveCell.Offset(1, 6) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-1]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,6)"
                ActiveCell.Offset(1, 7) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-1]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,7)"
                ActiveCell.Offset(1, 8) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-1]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,8)"
                ActiveCell.Offset(1, 9) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-1]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,9)"
                ActiveCell.Offset(1, 10) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-1]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,10)"
                ActiveCell.Offset(1, 11) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-1]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,11)"
                ActiveCell.Offset(1, 12) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-1]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,12)"
                
                ActiveCell.Offset(2, 0) = Year(Now()) & "NET SALES"
                ActiveCell.Offset(2, 1) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-2]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,1,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(2, 2) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-2]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,2,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(2, 3) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-2]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,3,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(2, 4) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-2]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,4,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(2, 5) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-2]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,5,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(2, 6) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-2]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,6,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(2, 7) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-2]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,7,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(2, 8) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-2]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,8,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(2, 9) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-2]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,9,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(2, 10) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-2]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,10,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(2, 11) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-2]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,11,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(2, 12) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-2]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,12,'Raw Data'!C6,""USA"")"
                
                ActiveCell.Offset(3, 0) = ("Gross Margin % ")
                ActiveCell.Offset(3, 1) = "=((R[-1]C)-(SUMIFS('Raw Data'!C26,'Raw Data'!C18,R[-3]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,1,'Raw Data'!C6,""USA"")))/R[-1]C"
                ActiveCell.Offset(3, 2) = "=((R[-1]C)-(SUMIFS('Raw Data'!C26,'Raw Data'!C18,R[-3]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,2,'Raw Data'!C6,""USA"")))/R[-1]C"
                ActiveCell.Offset(3, 3) = "=((R[-1]C)-(SUMIFS('Raw Data'!C26,'Raw Data'!C18,R[-3]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,3,'Raw Data'!C6,""USA"")))/R[-1]C"
                ActiveCell.Offset(3, 4) = "=((R[-1]C)-(SUMIFS('Raw Data'!C26,'Raw Data'!C18,R[-3]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,4,'Raw Data'!C6,""USA"")))/R[-1]C"
                ActiveCell.Offset(3, 5) = "=((R[-1]C)-(SUMIFS('Raw Data'!C26,'Raw Data'!C18,R[-3]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,5,'Raw Data'!C6,""USA"")))/R[-1]C"
                ActiveCell.Offset(3, 6) = "=((R[-1]C)-(SUMIFS('Raw Data'!C26,'Raw Data'!C18,R[-3]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,6,'Raw Data'!C6,""USA"")))/R[-1]C"
                ActiveCell.Offset(3, 7) = "=((R[-1]C)-(SUMIFS('Raw Data'!C26,'Raw Data'!C18,R[-3]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,7,'Raw Data'!C6,""USA"")))/R[-1]C"
                ActiveCell.Offset(3, 8) = "=((R[-1]C)-(SUMIFS('Raw Data'!C26,'Raw Data'!C18,R[-3]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,8,'Raw Data'!C6,""USA"")))/R[-1]C"
                ActiveCell.Offset(3, 9) = "=((R[-1]C)-(SUMIFS('Raw Data'!C26,'Raw Data'!C18,R[-3]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,9,'Raw Data'!C6,""USA"")))/R[-1]C"
                ActiveCell.Offset(3, 10) = "=((R[-1]C)-(SUMIFS('Raw Data'!C26,'Raw Data'!C18,R[-3]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,10,'Raw Data'!C6,""USA"")))/R[-1]C"
                ActiveCell.Offset(3, 11) = "=((R[-1]C)-(SUMIFS('Raw Data'!C26,'Raw Data'!C18,R[-3]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,11,'Raw Data'!C6,""USA"")))/R[-1]C"
                ActiveCell.Offset(3, 12) = "=((R[-1]C)-(SUMIFS('Raw Data'!C26,'Raw Data'!C18,R[-3]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,12,'Raw Data'!C6,""USA"")))/R[-1]C"
                
                ActiveCell.Offset(4, 0) = (Year(Now()) - 1) & " NET SALES"
                ActiveCell.Offset(4, 1) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-4]C1,'Raw Data'!C1,'sheet1'!r2c2,'Raw Data'!C3,1,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(4, 2) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-4]C1,'Raw Data'!C1,'sheet1'!r2c2,'Raw Data'!C3,2,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(4, 3) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-4]C1,'Raw Data'!C1,'sheet1'!r2c2,'Raw Data'!C3,3,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(4, 4) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-4]C1,'Raw Data'!C1,'sheet1'!r2c2,'Raw Data'!C3,4,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(4, 5) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-4]C1,'Raw Data'!C1,'sheet1'!r2c2,'Raw Data'!C3,5,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(4, 6) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-4]C1,'Raw Data'!C1,'sheet1'!r2c2,'Raw Data'!C3,6,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(4, 7) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-4]C1,'Raw Data'!C1,'sheet1'!r2c2,'Raw Data'!C3,7,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(4, 8) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-4]C1,'Raw Data'!C1,'sheet1'!r2c2,'Raw Data'!C3,8,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(4, 9) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-4]C1,'Raw Data'!C1,'sheet1'!r2c2,'Raw Data'!C3,9,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(4, 10) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-4]C1,'Raw Data'!C1,'sheet1'!r2c2,'Raw Data'!C3,10,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(4, 11) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-4]C1,'Raw Data'!C1,'sheet1'!r2c2,'Raw Data'!C3,11,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(4, 12) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-4]C1,'Raw Data'!C1,'sheet1'!r2c2,'Raw Data'!C3,12,'Raw Data'!C6,""USA"")"
                
                ActiveCell.Offset(5, 0) = (Year(Now()) - 2) & " NET SALES"
                ActiveCell.Offset(5, 1) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-5]C1,'Raw Data'!C1,'sheet1'!r2c1,'Raw Data'!C3,1,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(5, 2) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-5]C1,'Raw Data'!C1,'sheet1'!r2c1,'Raw Data'!C3,2,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(5, 3) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-5]C1,'Raw Data'!C1,'sheet1'!r2c1,'Raw Data'!C3,3,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(5, 4) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-5]C1,'Raw Data'!C1,'sheet1'!r2c1,'Raw Data'!C3,4,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(5, 5) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-5]C1,'Raw Data'!C1,'sheet1'!r2c1,'Raw Data'!C3,5,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(5, 6) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-5]C1,'Raw Data'!C1,'sheet1'!r2c1,'Raw Data'!C3,6,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(5, 7) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-5]C1,'Raw Data'!C1,'sheet1'!r2c1,'Raw Data'!C3,7,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(5, 8) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-5]C1,'Raw Data'!C1,'sheet1'!r2c1,'Raw Data'!C3,8,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(5, 9) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-5]C1,'Raw Data'!C1,'sheet1'!r2c1,'Raw Data'!C3,9,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(5, 10) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-5]C1,'Raw Data'!C1,'sheet1'!r2c1,'Raw Data'!C3,10,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(5, 11) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-5]C1,'Raw Data'!C1,'sheet1'!r2c1,'Raw Data'!C3,11,'Raw Data'!C6,""USA"")"
                ActiveCell.Offset(5, 12) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-5]C1,'Raw Data'!C1,'sheet1'!r2c1,'Raw Data'!C3,12,'Raw Data'!C6,""USA"")"
                Columns.AutoFit
            End With
              
            ThisWorkbook.Sheets("Values DO NOT ULTER").Activate
        ActiveCell.Offset(1, 0).Activate
       
  ''''''''''''''''''''''''Chaecing on this yet not looping correctly'''''''''''''''''''''''''''''''''''''''''
       
     End If
        
    Loop
    Worksheets("Values DO NOT ULTER").Visible = False

   Application.ScreenUpdating = True
  

  
End Sub
 
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
In this Sumifs formula, you are referencing entire columns. Columns 25, 18, 1, and 3 (or Column Y, R A, and C). You are referencing 1,048,576 rows. If you make a dynamic named for each of those columns of data, the formulas don't have to read all of those rows.
Code: 
ActiveCell.Offset(1, 1) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-1]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,1)"

You may already know this, but SUMIFS formulas need all the Sum refs and criteria refs to have the same amount of rows. So, the dynamic ranges must be made so that they are returning the same number of rows. If your database has blank cells within a given column, you may want to use a single column to "Count" the number of true rows. If column A always has no blank cells then I would use that as my Anchor counting column. Based on my examples of dynamic ranges, I would change the formula to create a dynamic range for Column Y to this:
=offset($Y$1,1,0,match("zzzzzzzz",$A:$A)-row($A$1),1)
If you named that Range "ColY" (I know, boring!!!), you would use it in your VBA like this:
Code: 
ActiveCell.Offset(1, 1) = "=SUMIFS(ColY,ColR,R[-1]C1,ColA,Year(Now()),ColC"

Make sure to make a "Workbook" scoped named range, not a Worksheet scope. That way you can reference it from any sheet and not have to specify the sheet name.

I think these are much easier to read in a formula anyway.

Jeff
 
Upvote 0
Jeffrey Mahoney said:
In this Sumifs formula, you are referencing entire columns. Columns 25, 18, 1, and 3 (or Column Y, R A, and C). You are referencing 1,048,576 rows. If you make a dynamic named for each of those columns of data, the formulas don't have to read all of those rows.
Code: 
ActiveCell.Offset(1, 1) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C18,R[-1]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,1)"

You may already know this, but SUMIFS formulas need all the Sum refs and criteria refs to have the same amount of rows. So, the dynamic ranges must be made so that they are returning the same number of rows. If your database has blank cells within a given column, you may want to use a single column to "Count" the number of true rows. If column A always has no blank cells then I would use that as my Anchor counting column. Based on my examples of dynamic ranges, I would change the formula to create a dynamic range for Column Y to this:
=offset($Y$1,1,0,match("zzzzzzzz",$A:$A)-row($A$1),1)
If you named that Range "ColY" (I know, boring!!!), you would use it in your VBA like this:
Code: 
ActiveCell.Offset(1, 1) = "=SUMIFS(ColY,ColR,R[-1]C1,ColA,Year(Now()),ColC"

Make sure to make a "Workbook" scoped named range, not a Worksheet scope. That way you can reference it from any sheet and not have to specify the sheet name.

I think these are much easier to read in a formula anyway.

Jeff
Click to expand...

I have never used dynamic name or offset im new to VBA but the part of the formula im confused on is match("zzzzzzzz",$A:$A)-row($A$1) I don't understand what I am matching to column A.
 
Upvote 0
You can go over the worksheet row limit by using PowerQuery/PowerPivot and the Data Model.
Power Query(2010/2013) or Get and Transform(Excel 2016) updating the table is easy.
Then using Table Names cures your dynamic ranges. Even if not using PowerQuery,, using Table will gain several benefits.
 
Upvote 0
match("zzzzzzzz",$A:$A)-row($A$1)
Click to expand...
That is finding the last row in a column that is less than zzzzzzz and then you have to subtract the header row because you are using the OFFSET formula. Use the match formula by itself, it will return the row number of the last non-blank cell.
 
Upvote 0
You must log in or register to reply here.

Similar threads

larinda4
  • Question
VBA Formula Is Blank (Formula Too Long?)
Replies
1
Views
180
larinda4
larinda4
baleshst
  • Question
Workbook takes too long to calcuate
Replies
3
Views
355
Jalize
J
I
  • Question
Current Code taking 10 Hours to run. Need Help to Optimize
Replies
5
Views
321
ItalianPlatinum
I
A
  • Solved
Need VBA to automatically copy paste rows based factory selected
Replies
8
Views
264
Alex Blakenburg
A
L
  • Solved
VBA Find Loop takes too long
2
Replies
12
Views
876
Lil Stinker
L

Forum statistics

Threads
1,216,101
Messages
6,128,845
Members
449,471
Latest member
lachbee

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top