macro takes too long

[kylefoley76]

This macro is taking about 1 second to loop through just 1 iteration and it is a very simple macro. With about 700 interations it will take more than 11 minutes.

What is wrong?

Sub dates()


Dim thedate As Date


thedate = ActiveCell.Offset(-1).Value
Dim i As Integer
Application.ScreenUpdating = False




Do Until ActiveCell.Offset(0, 1) = ""
i = i + 1




    If ActiveCell.Offset(0, 2).Value <> "x" Then
    ActiveCell = thedate
    Else
    thedate = thedate + 1
    ActiveCell = thedate
    End If
ActiveCell.Offset(1).Select




Loop


Application.ScreenUpdating = True


End Sub

 

[kylefoley76]

What it does is that it prints the same date in the e column as the cell above, unless there is an x in the adjacent g column. Here is a screen shot

 

[shg]

Based on that, perhaps

Sub kyle()
    Dim iRow As Long

    For iRow = 2 To Cells(Rows.Count, "G").End(xlUp).Row
        If Cells(iRow, "G") = "x" Then
            Cells(iRow, "E").Value = Cells(iRow - 1, "E").Value + 1
        Else
            Cells(iRow, "E").Value = Cells(iRow - 1, "E").Value
        End If
    Next iRow
End Sub

or

Sub kyle()
    Dim iRow        As Long

    For iRow = 2 To Cells(Rows.Count, "G").End(xlUp).Row
        Cells(iRow, "E").Value = Cells(iRow - 1, "E").Value - (Cells(iRow, "G") = "x")
    Next iRow
End Sub

 

[shg]

Reckon those need to start in row 3.

 

[kylefoley76]

That formula worked with just one minor change but it is still taking me 85 seconds to do 50 iterations. It was taking 93 seconds so I tried eliminating the following formula on another worksheet which I would think would be irrelevant.

=SUMPRODUCT([time.xlsm]time!$I$1418:[time.xlsm]time!$I$39435,--([time.xlsm]time!$E$1418:[time.xlsm]time!$E$39435=$B4),--([time.xlsm]time!$G$1418:[time.xlsm]time!$G$39435=C$2))

That formula only appeared in about 50 cells. I then removed the following formula in about 30,000 cells:


=VLOOKUP(G35755,key!$A$1:key!$B$75,2,0)

But that only gained 4 seconds in time. Andrew, the legend, was talking about something firing events that might be slowing things down. Do you know what he means.

 

[Smitty]

Andrew was talking about setting calculation to Manual.

 

[shg]

It should run in less than the blink of an eye.

Try this:

Sub kyle()
    Dim iRow        As Long

    On Error GoTo Oops
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    For iRow = 3 To Cells(Rows.Count, "G").End(xlUp).Row
        If Cells(iRow, "G") = "x" Then
            Cells(iRow, "E").Value = Cells(iRow - 1, "E").Value + 1
        Else
            Cells(iRow, "E").Value = Cells(iRow - 1, "E").Value
        End If
    Next iRow

Oops:
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub

 

[kylefoley76]

Thanks, that did it. 600 iterations in 1.9 seconds. Excel for PC is much faster than Excel for Mac, so with Excel for PC it probably would have done it in .5 seconds.

Do you know the answer to this problem?

http://www.mrexcel.com/forum/excel-...ksheet-function-into-macro-2.html#post3951493

 

[shg]

You're welcome.

 

[Rick Rothstein]

Thanks, that did it. 600 iterations in 1.9 seconds. Excel for PC is much faster than Excel for Mac, so with Excel for PC it probably would have done it in .5 seconds.

Just out of curiosity, how does this code compare time-wise? Note the red highlighted code... it assumes cell E2 contains the starting date, so if that assumption is wrong, then change the E2 to the cell address which does contain the date.

Sub Kyle2()
  Dim X As Long, Dte As Date, Cat As Variant, Dates As Variant
  Dte = [COLOR=#FF0000][B]Range("E2").Value[/B][/COLOR]
  Cat = Range("G3", Cells(Rows.Count, "G").End(xlUp))
  ReDim Dates(1 To UBound(Cat), 1 To 1)
  For X = 1 To UBound(Cat)
    If Cat(X, 1) = "x" Then Dte = Dte + 1
    Dates(X, 1) = Dte
  Next
  Range("E3").Resize(UBound(Dates)).Value = Dates
End Sub

 

[Andrew Poulsom]

Nothing to do with speed, but why are you using this syntax?

[time.xlsm]time!$I$1418:[time.xlsm]time!$I$39435
key!$A$1:key!$B$75

It should be:

[time.xlsm]time!$I$1418:$I$39435
key!$A$1:$B$75