Hey guys,

I've come across twice now where the same macro, when run on two separate instances, run at very different speeds. (20 minutes vs. 30 seconds.) This timing, is, of course, significant, and I would love to know if there are settings on excel or something similar that I am accidentally flipping on or off.

Here is the code below, though, again, nothing was changed between the two runs. 

Sub selecttimes(variableinlist, indexnumber)

Dim calculatorname As String
Dim firstrow As Long
Dim lastrow As Long
Dim Lrow As Long
Dim array1(1 To 45) As Double
Dim array1b(1 To 45) As Double
Dim array1c(1 To 45) As Double
Dim array1d(1 To 45) As Double
Dim array2(1 To 45) As Double
Dim array2b(1 To 45) As Double
Dim array2c(1 To 45) As Double
Dim array2d(1 To 45) As Double
Dim array3(1 To 45) As Double
Dim array3b(1 To 45) As Double
Dim array3c(1 To 45) As Double
Dim array3d(1 To 45) As Double
Dim array4(1 To 45) As Double
Dim array4b(1 To 45) As Double
Dim array4c(1 To 45) As Double
Dim array4d(1 To 45) As Double
Dim startmonth As Long
Dim counter1a As Long
Dim counter1b As Long
Dim counter2a As Long
Dim counter2b As Long
Dim counter3a As Long
Dim counter3b As Long
Dim counter4a As Long
Dim counter4b As Long
Dim y1 As Double
Dim z1 As Double
Dim y2 As Double
Dim z2 As Double
Dim y3 As Double
Dim z3 As Double
Dim y4 As Double
Dim z4 As Double


'DEFINITIONS
calculatorname = ThisWorkbook.Name
startmonth = Range("startmonth")
counter1a = 0
counter1b = 0
counter2a = 0
counter2b = 0
counter3a = 0
counter3b = 0
counter4a = 0
counter4b = 0
y1 = 0
z1 = 0
y2 = 0
z2 = 0
y3 = 0
z3 = 0
y4 = 0
z4 = 0

'PULL TIME DATA INTO SEPARATE ARRAYS

    'Loop data into separate arrays, based on time
    With Workbooks(calculatorname).Sheets("Historical")
        lastrow = Application.WorksheetFunction.Match(startmonth, Range("historicaldates"), 0) + 6
        firstrow = Range("c7").Row
            i = 1
            j = 1
            k = 1
            l = 1
    'Loop from Lastrow to Firstrow (bottom to top)
    For Lrow = lastrow To firstrow Step -1
         With .Cells(Lrow, "C")
                If .Value = "1" Then
                        If .Cells.Offset(1, 0) <> "1" And .Cells.Offset(-1, indexnumber) <> "" And i < 45 Then
                            If counter1a > 0 Then
                            z1 = y1 - .Cells.Offset(0, indexnumber)
                            ElseIf counter1a = 0 Then
                            counter1a = counter1a + 1
                            End If
                            End If
                        If .Cells.Offset(-1, 0) <> "1" And .Cells.Offset(-1, indexnumber) <> "" And i < 45 Then
                            y1 = .Cells.Offset(0, indexnumber) + z1
                            End If
                        If .Cells.Offset(-1, 0) = "1" And .Cells.Offset(-1, indexnumber) <> "" And i < 45 Then
                            array1(i) = .Cells.Offset(0, -1)
                            array1b(i) = .Cells.Offset(0, indexnumber) + z1
                            i = i + 1
                            End If
                ElseIf .Value = "2" Then
                        If .Cells.Offset(1, 0) <> "2" And .Cells.Offset(-1, indexnumber) <> "" And j < 45 Then
                            If counter2a > 0 Then
                            z2 = y2 - .Cells.Offset(0, indexnumber)
                            ElseIf counter2a = 0 Then
                            counter2a = counter2a + 1
                            End If
                            End If
                        If .Cells.Offset(-1, 0) <> "2" And .Cells.Offset(-1, indexnumber) <> "" And j < 45 Then
                            y2 = .Cells.Offset(0, indexnumber) + z2
                            End If
                        If .Cells.Offset(-1, 0) = "2" And .Cells.Offset(-1, indexnumber) <> "" And j < 45 Then
                            array2(j) = .Cells.Offset(0, -1)
                            array2b(j) = .Cells.Offset(0, indexnumber) + z2
                            j = j + 1
                            End If
                ElseIf .Value = "3" Then
                        If .Cells.Offset(1, 0) <> "3" And .Cells.Offset(-1, indexnumber) <> "" And k < 45 Then
                            If counter3a > 0 Then
                            z3 = y3 - .Cells.Offset(0, indexnumber)
                            ElseIf counter3a = 0 Then
                            counter3a = counter3a + 1
                            End If
                            End If
                        If .Cells.Offset(-1, 0) <> "3" And .Cells.Offset(-1, indexnumber) <> "" And k < 45 Then
                            y3 = .Cells.Offset(0, indexnumber) + z3
                            End If
                        If .Cells.Offset(-1, 0) = "3" And .Cells.Offset(-1, indexnumber) <> "" And k < 45 Then
                            array3(k) = .Cells.Offset(0, -1)
                            array3b(k) = .Cells.Offset(0, indexnumber) + z3
                            k = k + 1
                            End If
                ElseIf .Value = "4" Then
                        If .Cells.Offset(1, 0) <> "4" And .Cells.Offset(-1, indexnumber) <> "" And l < 45 Then
                            If counter4a > 0 Then
                            z4 = y4 - .Cells.Offset(0, indexnumber)
                            ElseIf counter4a = 0 Then
                            counter4a = counter4a + 1
                            End If
                            End If
                        If .Cells.Offset(-1, 0) <> "4" And .Cells.Offset(-1, indexnumber) <> "" And l < 45 Then
                            y4 = .Cells.Offset(0, indexnumber) + z4
                            End If
                        If .Cells.Offset(-1, 0) = "4" And .Cells.Offset(-1, indexnumber) <> "" And l < 45 Then
                            array4(l) = .Cells.Offset(0, -1)
                            array4b(l) = .Cells.Offset(0, indexnumber) + z4
                            l = l + 1
                            End If
                ElseIf .Value = "" Then
                    Sheets("EV_" & indexnumber).Range("b11:b46") = WorksheetFunction.Transpose(array1)
                    Sheets("EV_" & indexnumber).Range("C11:C46") = WorksheetFunction.Transpose(array1b)
                    Sheets("EV_" & indexnumber).Range("g11:g46") = WorksheetFunction.Transpose(array2)
                    Sheets("EV_" & indexnumber).Range("H11:H46") = WorksheetFunction.Transpose(array2b)
                    Sheets("EV_" & indexnumber).Range("l11:l46") = WorksheetFunction.Transpose(array3)
                    Sheets("EV_" & indexnumber).Range("M11:M46") = WorksheetFunction.Transpose(array3b)
                    Sheets("EV_" & indexnumber).Range("Q11:Q46") = WorksheetFunction.Transpose(array4)
                    Sheets("EV_" & indexnumber).Range("R11:R46") = WorksheetFunction.Transpose(array4b)
                End If
        End With
    Next Lrow
    
    End With
    
    'Erase arrays
    Erase array1
    Erase array1b
    Erase array1c
    Erase array1d
    Erase array2
    Erase array2b
    Erase array2c
    Erase array2d
    Erase array3
    Erase array3b
    Erase array3c
    Erase array3d
    Erase array4
    Erase array4b
    Erase array4c
    Erase array4d



End Sub

Sub fliparrays(indexnumber)
Dim c As Range
Dim d As Range
Dim e As Range
Dim f As Range
Dim varname As String

'FLIP ARRAYS TO SORT TIME DATA BY DATE

    Sheets("EV_" & indexnumber).Activate

    'Flip and name ranges for time 1
    Range("B11:C46").Select
    Range("B11:B46").Name = "Dates_TIME_1"
    Range("C11:C46").Name = ActiveSheet.Name & "_1"
    Selection.Sort Key1:=Range("B10"), Order1:=xlAscending

    'Flip and name ranges for Time 2
    Range("G11:H46").Select
    Range("G11:G46").Name = "Dates_Time_2"
    Range("H11:H46").Name = ActiveSheet.Name & "_2"
    Selection.Sort Key1:=Range("G10"), Order1:=xlAscending
    
    'Flip and name ranges for time 3
    Range("L11:M46").Select
    Range("L11:L46").Name = "Dates_Time_3"
    Range("M11:M46").Name = ActiveSheet.Name & "_3"
    Selection.Sort Key1:=Range("L10"), Order1:=xlAscending
    
    'Flip and name ranges for Time 4
    Range("Q11:R46").Select
    Range("Q11:Q46").Name = "Dates_Time_4"
    Range("R11:R46").Name = ActiveSheet.Name & "_4"
    Selection.Sort Key1:=Range("Q10"), Order1:=xlAscending
    
End Sub