Macro that ranks data as a percent rank

[alocke]

Hi guys,
I've been working on a project and have hit a little bit of a brick wall. I have many problems at the moment, but the following is the most important thing I have to deal with.

If someone can do this I'll be amazed as I've asked so many people and no one has any idea how to go about completing this.

I'm performing a percentrank on 400 odd stocks. All it entails is I need a percentrank for values U8, U208, U408, .... ,U(8+200n) Where n = 0,1,2...... all the way to 400
ie PERCENTRANK = (U8:U(8+200n) , U8) and I want to do this for other values which match this criteria so (U8:U(8+200n) , U208) etc up until (U8 : U(8+200n), U(8+200n))

-All Data is on one spreadsheet.
-Its divided into stocks which are one row apart and take a total of around 150 rows each.

So basically this will not include values that don't match U(8+200n).

All other cells between are filled and the same needs to be done with them, but as different array indexes, so I need to somehow skip out these values when calculating the Percent Rank of each particular array index.

I then want to do the same for U(9+200n), U(10+200n) until U(43+200n)

I've done this so far:

Function gennArr()

Dim qArr()
i = 8
n = 0

Do
    ReDim Preserve qArr(n)
    qArr(n) = "U" & i & ":U" & (i + 35)
    i = i + 200
    n = n + 1

Loop Until n = 2

gennArr = qArr
End Function

                
                
                

Public Sub testrank1234()
ap = 1
arr = gennArr

For ap = LBound(arr) To UBound(arr)
    Formatting (arr(ap))

Dim rng1 As Range

Set rng1 = Range(Cells(i, 21), Cells(400 + i, 21))


Cells(i, 22).Formula = "=PERCENTRANK(" & rng1.Address(False, False) & "," & Cells(i, 21).Address(False, False) & ")"
    
Dim j As Integer
    j = i + (200 * n)
    
If Not j Then Range(Cells(i , 22)) , (35 + i, 22)).ClearContents

Next

End Sub

[B][FONT=Arial][SIZE=2][COLOR=#000000] [/COLOR][/SIZE][/FONT][/B]

I know theres a few things wrong with this such as If I clear contents after I calculate percentrank for one array index (U8:U(8+200*n)) and then move on to the next one (U9:U(9+200*n)) all the ranks of the previous array index so (U8:U(8+200*n)) will be cleared. The code also doesn't seem to understand my defining of j, so usually when I run this script everything on the entire spreadsheet gets removed.

I've probably explained this quite badly, so let me know if sending you a screenshot would help. If you'd like me to give more information on the project, let me know.

If you don't have a direct answer - but if you just know a different way to solve it please let me know as its quite important I finish this reasonably soon. Is it best to write it as an array index.

I've probably over complicated it, as I've never done anything as advanced on VBA before - Cheers In advance for any help,
Alex.

​