|You were close!|
Where you have this:
"=RANK(RC[-1],R" & firstRw & "H3:R" & firstRw + nxtRw - 1 & "H3)"
you should have this:
"=RANK(RC[-6],R" & firstRw & "C8:R" & firstRw + nxtRw - 1 & "C8)"
Since the formula within VBA is written in R1C1 notation, the "C" doesn't mean Column C it means Column(whatever number follows it).
e.g. C1 means Column A, C3 means Column C, C8 means Column H.
In addition, with your example data in Column C and the Ranking formula in Column D, C[-1] means look back 1 column. Since you are now ranking data from Column H in Column N, the formula has to look back 6 columns by using C[-6].
Start the macro recorder and enter a RANK formula manually to rank some values. Then go look at what the recorder wrote.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.