Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2007

    Unanswered: Suddenly Slow Code Execution

    Hey guys and girls,

    I have a problem with slow code execution on a spreadsheet I use quite frequently.

    The code is simple, all it does is run through a table of data looking for specific criteria to be true in each Row (an autofilter essentially) then populate a table with positive matches. Thanks to JerryDal for this code you showed me how to do it initially.

    Here it is:

    Sub RFS_populate()
    Dim xx As Integer, bb As Integer, mm As Integer
    Application.Calculation = xlCalculationManual
    xx = 6
    bb = 4
    mm = 2
    code = "RFS" & Cells(2, 3).Value & Cells(1, 3).Value
    Do While Cells(xx, 2).Value <> ""
        Cells(xx, 2).Value = ""
        Cells(xx, 3).Value = ""
        Cells(xx, 4).Value = ""
        Cells(xx, 5).Value = ""
        Cells(xx, 6).Value = ""
        Cells(xx, 7).Value = ""
        Cells(xx, 8).Value = ""
        Cells(xx, 9).Value = ""
        xx = xx + 1
    mm = 6
    Do While Sheets("Inspections").Cells(bb, 8).Value <> ""
        codechk = Sheets("Inspections").Cells(bb, 3).Value & Sheets("Inspections").Cells(bb, 10).Value & Sheets("Inspections").Cells(bb, 9).Value
        If codechk = code Then
            Cells(mm, 2).Value = Sheets("Inspections").Cells(bb, 5).Value
            Cells(mm, 3).Value = Sheets("Inspections").Cells(bb, 2).Value
            Cells(mm, 4).Value = Sheets("Inspections").Cells(bb, 8).Value
            Cells(mm, 5).Value = Sheets("Inspections").Cells(bb, 11).Value
            Cells(mm, 6).Value = Sheets("Inspections").Cells(bb, 12).Value
            Cells(mm, 7).Value = Sheets("Inspections").Cells(bb, 13).Value
            Cells(mm, 8).Value = Sheets("Inspections").Cells(bb, 14).Value
            Cells(mm, 9).Value = Sheets("Inspections").Cells(bb, 15).Value
            mm = mm + 1
        End If
    bb = bb + 1
    Application.Calculation = xlCalculationAutomatic
    End Sub
    Now, normally this code executes almost instantly, However, in adding another table that is populated using the same information and SUMPRODUCT my code is immediately slowed to a crawl. I can speed it up by quickly clicking the mouse anywhere on the sheet (which is wierd right?).

    Attached is the added worksheet, please excuse me not putting the file up completely as i cannot.
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2008
    London, UK
    Hi Matt,

    I can't view your attachment?

  3. #3
    Join Date
    Mar 2007
    Sorry I've been meaning to tie this one up for a while, I no longer have the file but I did eventually work out why it was doing this.

    Unfortunately I can't for the life of me remember how but essentially the problem was down to inefficent code.

    Very sorry for the loose end here.
    Last edited by MatthewM; 09-26-11 at 00:34.

Tags for this Thread

Posting Permissions

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