Looking for VBA Code to rank Group data

[KWL]

I prepare a daily spreadsheet that varies in length (up to 3000 rows)…. Column A contains defined “groups” of various lengths beginning in A2 with a blank row separator between each group…. Column P contains numeric values unique to each group…. In Column Q, I want to rank each value in Column P (from low to high) with “low” = 1.
I have been using the following formula located in P2:
=IF(ISBLANK(A2),””,1+SUMPRODUCT(($A$2:$A$3000=A2)*($P$2:$P$3000<P2)))
I copy and drag to the bottom of the sheet…. For some reason, I am getting lots of random errors?
Instead of the above, I would like to use VBA Code…. I don’t want to use the Macro recorder because of the random errors…. Can anyone help?
Thanks in advance,
KWL

 

[mirabeau]

Hi,

Does this code work for you?

Sub aVBAcode()
Dim e As Range, q As Range
Application.ScreenUpdating = False
Set e = Range("A2")
Do While e.Row < Rows.Count
If e(2) = "" Then
    Set q = e.Offset(, 15)
    Set e = e.End(4)
Else
    Set q = Range(e, e.End(4)).Offset(, 15)
    Set e = e.End(4).End(4)
End If
q.Offset(, 1) = "=RANK(" & q.Address & "," & q.Address & ",1)"
Loop
Application.ScreenUpdating = True
End Sub

 

[KWL]

Awesome.... Works to perfection!

Thank you so very very much for the help!

Best regards,

KWL