Excel VBA: How to make faster calculation on Ranking by Formula Array? RRS feed

  • Question

  • Hi,

    I have an Excel VBA app. It takes 4 minutes to Rank Calculation for 999 row numbers. I have Schools in D column and total GPA is in AF column and resulting rank is in AH column (34 th column). Rank Range is AF4:AF1003 (starting row is 4 and ending row is 1003 for eg but depends on data size) Rank Criteria is in D column. to be rank value is AF column. j means respecting row number. Sheet11.cells(j, 34) is resulting cells. How to make faster calculation? It gives correct rank based on each school students (in not skipping number) but time takes more. j and RI are integer variables.

    Do while not SheetX(j, 1).Value = vbNullString
    SheetX.Cells(j, 34).FormulaArray = "=SUM(IF($D$4:$D$" & RI & "=$D" & j & ",IF($AF" & j & "<$AF$4:$AF$" & RI & ",1/COUNTIFS($D$4:$D$" & RI & ",$D" & j & ",$AF$4:$AF$" & RI & ",$AF$4:$AF$" & RI  & "))))+1"
    j = j + 1

    There is another column for ranking overall GPA (All School) as:

    SheetX.Cells(j, 33).FormulaArray = "=SUM(IF(AF" & j & "<$AF$4:$AF$" & RI & ",1/COUNTIF($AF$4:$AF$" & RI & ",$AF$4:$AF$" & RI & ")))+1"

    Within the Same Do While Loop


    Needs immediate help. I am going to demo beta this week.

    - SK

    SK Bhattarai

    • Edited by developersk Wednesday, December 13, 2017 5:34 AM Another Similar Problem
    Wednesday, December 13, 2017 5:15 AM

All replies

  • I would need a copy of your workbook to test and be sure but I believe that you should be able to put the formula in the first cell of the range and then copy that cell and paste to the remaining range.

    You will need to identify the first cell in which to insert the formula and then identify the range from the 2nd cell to the last cell and then copy the first cell and paste the formula.

    The above will get rid of the loop and speed up calculation but without an example of data I can't be sure.

    You could also set calculation to Manual before the loop starts and then set to Automatic after the loop is finished.

    Application.Calculation = xlManual      'Insert before the loop

    Application.Calculation = xlAutomatic   'Insert after the loop

    I believe that the speed is slowed because all the formulas are calculating each time a formula is inserted from within the loop and the above method will allow it to only calculate once when finished.

    Regards, OssieMac

    • Proposed as answer by Wouter Defour Wednesday, December 13, 2017 12:16 PM
    Wednesday, December 13, 2017 11:02 AM