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

Loop

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

Thanks

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

- SK

SK Bhattarai