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
It seems like a very long time. Do you need to reference the whole column of Y, L, A, and F? I have worksheets with thousands of those Sumifs functions and it doesn't take but a few seconds. Most of my data ranges only go to about 100,000 or so.

Maybe you need to create some dynamic ranges!?

Jeff
 
Upvote 0
Also, does your VBA have calculation set to manual while your formulae are entered ???
 
Upvote 0
Jeffrey Mahoney said:
It seems like a very long time. Do you need to reference the whole column of Y, L, A, and F? I have worksheets with thousands of those Sumifs functions and it doesn't take but a few seconds. Most of my data ranges only go to about 100,000 or so.

Maybe you need to create some dynamic ranges!?

Jeff
Click to expand...
I do need to reference the whole column because it is looking for the match to another cell so it is looking for all of the cells that have apples in column L the year in column A if it is in USA in column F if that all matches then it should add what is in column y for all of them. here is my code for the formula part

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).Range("A1").Select
          ActiveCell.FormulaR1C1 = "January"
          ActiveCell.Offset(0, 1).Range("A1").Select
          ActiveCell.FormulaR1C1 = "Febuary"
          ActiveCell.Offset(0, 1).Range("A1").Select
          ActiveCell.FormulaR1C1 = "March"
          ActiveCell.Offset(0, 1).Range("A1").Select
          ActiveCell.FormulaR1C1 = "April"
          ActiveCell.Offset(0, 1).Range("A1").Select
          ActiveCell.FormulaR1C1 = "May"
          ActiveCell.Offset(0, 1).Range("A1").Select
          ActiveCell.FormulaR1C1 = "June"
          ActiveCell.Offset(0, 1).Range("A1").Select
          ActiveCell.FormulaR1C1 = "July"
          ActiveCell.Offset(0, 1).Range("A1").Select
          ActiveCell.FormulaR1C1 = "August"
          ActiveCell.Offset(0, 1).Range("A1").Select
          ActiveCell.FormulaR1C1 = "September"
          ActiveCell.Offset(0, 1).Range("A1").Select
          ActiveCell.FormulaR1C1 = "October"
          ActiveCell.Offset(0, 1).Range("A1").Select
          ActiveCell.FormulaR1C1 = "November"
          ActiveCell.Offset(0, 1).Range("A1").Select
          ActiveCell.FormulaR1C1 = "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,Year(Now()),'Raw Data'!C3,1)"
                ActiveCell.Offset(1, 2) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-1]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,2)"
                ActiveCell.Offset(1, 3) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-1]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,3)"
                ActiveCell.Offset(1, 4) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-1]C1,'Raw Data'!C1,Year(Now()) ,'Raw Data'!C3,4)"
                ActiveCell.Offset(1, 5) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-1]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,5)"
                ActiveCell.Offset(1, 6) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-1]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,6)"
                ActiveCell.Offset(1, 7) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-1]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,7)"
                ActiveCell.Offset(1, 8) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-1]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,8)"
                ActiveCell.Offset(1, 9) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-1]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,9)"
                ActiveCell.Offset(1, 10) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-1]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,10)"
                ActiveCell.Offset(1, 11) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,R[-1]C1,'Raw Data'!C1,Year(Now()),'Raw Data'!C3,11)"
                ActiveCell.Offset(1, 12) = "=SUMIFS('Raw Data'!C25,'Raw Data'!C14,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'!C14,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'!C14,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'!C14,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'!C14,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'!C14,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'!C14,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'!C14,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'!C14,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'!C14,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'!C14,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'!C14,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'!C14,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'!C14,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'!C14,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'!C14,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'!C14,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'!C14,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'!C14,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'!C14,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'!C14,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'!C14,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'!C14,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'!C14,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'!C14,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'!C14,R[-4]C1,'Raw Data'!C1,(Year(Now()) - 1),'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,(Year(Now()) - 1),'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,(Year(Now()) - 1),'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,(Year(Now()) - 1),'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,(Year(Now()) - 1),'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,(Year(Now()) - 1),'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,(Year(Now()) - 1),'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,(Year(Now()) - 1),'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,(Year(Now()) - 1),'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,(Year(Now()) - 1),'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,(Year(Now()) - 1),'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,(Year(Now()) - 1),'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,(Year(Now()) - 1),'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,(Year(Now()) - 1),'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,(Year(Now()) - 1),'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,(Year(Now()) - 1),'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,(Year(Now()) - 1),'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,(Year(Now()) - 1),'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,(Year(Now()) - 1),'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,(Year(Now()) - 1),'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,(Year(Now()) - 1),'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,(Year(Now()) - 1),'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,(Year(Now()) - 1),'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,(Year(Now()) - 1),'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,(Year(Now()) - 2),'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,(Year(Now()) - 2),'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,(Year(Now()) - 2),'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,(Year(Now()) - 2),'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,(Year(Now()) - 2),'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,(Year(Now()) - 2),'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,(Year(Now()) - 2),'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,(Year(Now()) - 2),'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,(Year(Now()) - 2),'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,(Year(Now()) - 2),'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,(Year(Now()) - 2),'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,(Year(Now()) - 2),'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
2 things to note.
1. did you read post #3
2. you need to remove most references of select.selection....this

Code: 
ActiveCell.Offset(2, 1).value = "January"

is better than
Code: 
ActiveCell.Offset(2, 1).Range("A1").Select
          ActiveCell.FormulaR1C1 = "January"

AND


you don't need entire columns as Jeff has already mentioned......simply use a reference to the last row to make the formula dynamic or use an arbitrary value.
So instead of "A:A".....you could narrow ir down to say "A1:A20000"
 
Upvote 0
Michael M said:
Also, does your VBA have calculation set to manual while your formulae are entered ???
Click to expand...
No I do not have them set to manual. how would I do that and how will that help? Doesn't it still take a long time to calculate once the macro is done running?I can have up to 20 sheets and over 1,000 formulas per sheet. as soon as the values are calculated I can copy and past the value only because I don't need it to keep calculating.
 
Last edited:
Upvote 0
Michael M said:
2 things to note.
1. did you read post #3
2. you need to remove most references of select.selection....this

Code: 
ActiveCell.Offset(2, 1).value = "January"

is better than
Code: 
ActiveCell.Offset(2, 1).Range("A1").Select
          ActiveCell.FormulaR1C1 = "January"

AND


you don't need entire columns as Jeff has already mentioned......simply use a reference to the last row to make the formula dynamic or use an arbitrary value.
So instead of "A:A".....you could narrow ir down to say "A1:A20000"
Click to expand...
I do need to use the A:A because the raw data can be over a million rows of data. I did change the top part of the formulas like you suggested but still takes way to long. it is maxing out my CPU for a long time and IT is getting a little mad at me. lol
 
Upvote 0
you could set calculation to manual at the beginning and back to auto before you paste values by using
Code: 
Application.Calculation = xlCalculationManual
at the start
AND
Code: 
Application.Calculation = xlCalculationAutomatic
before paste values
The reapply the same process after the paste values

I note you are still using Select.Selection in the code which will slow things down !
You could also the last row of the longest column and refer to that dynamically, rather than the entire column....for example
if column "A" was the longest, you would include in your code at the beginning
dim lr as long
lr = cells(rows.count, "A").end(xlup).row

then any reference in the formulae, instead of being say "A:A", it would be something like "A1:A" & lr
 
Upvote 0
Michael M said:
you could set calculation to manual at the beginning and back to auto before you paste values by using
Code: 
Application.Calculation = xlCalculationManual
at the start
AND
Code: 
Application.Calculation = xlCalculationAutomatic
before paste values
The reapply the same process after the paste values

I note you are still using Select.Selection in the code which will slow things down !
You could also the last row of the longest column and refer to that dynamically, rather than the entire column....for example
if column "A" was the longest, you would include in your code at the beginning
dim lr as long
lr = cells(rows.count, "A").end(xlup).row

then any reference in the formulae, instead of being say "A:A", it would be something like "A1:A" & lr
Click to expand...
I did fix the Select.section just didn't post the updated code. How can you use Last Row in a Sumifs formula? could you please give me an example. I did find one thing I am using
Year(Now()) and I found out that it trys to recalculate everything I try to put something in another cell so I changed that to reference a cell now rather than over a 1,000 Year(Now()) that seemed to help a little.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Reduce size of (or speed up) excel spreadsheet
Replies
5
Views
338
helpcharity
H
Asbestos_Jen
  • Solved
Speed up slow array index calculations
Replies
3
Views
184
Scott Huish
Scott Huish
R
  • Question Question
Time sheet formulas
Replies
2
Views
473
rodnaaaaay
R
A
  • Question Question
Is there a way to speed this up?
Replies
4
Views
370
jolivanes
jolivanes
U
  • Question Question
Excel Activate workbook1/sheet, Copy/paste row in workbook2/sheet, activate autofilter, return value and loop
Replies
0
Views
212
user29383902
U

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