I am having a few problems trying to reference a variable column in a;
..FormulaR1C1 ="=RC[?]" or should it be .Formula="=RC[?]" not sure on
difference.
either way the ? needs to be replaced with the column count offset, this
would be easy if the column was fixed, but it will vary from the source.

The column needed should always be equal to say;
SpecialCells(xlLastCell).Column -1
but I am not sure if this can be used within the RC[?] formula or if you can
set up a name which equals this column number and reference the name in the
RC[?] formula.

The extract of my macro is below and any help would be great.

New_Start_cell = "B4"
Opening_Row_Count = 5

With Sheets("Pivot")
New_Last_Row = .Range(New_Start_cell).End(xlDown).Row
Set Rank_Range = .Range(.Range(New_Start_cell).Offset(1, -1),
..Cells(New_Last_Row - 1, "A"))
End With

For Each Cell In Rank_Range
With Sheets("Pivot")
.Range("A" & Opening_Row_Count).FormulaR1C1 =
"=RANK(RC[?],Rank_Range)"
Opening_Row_Count = Opening_Row_Count + 1
End With
Next Cell