How to create variable cell ranges in formulas eg in Rank

[IanPM]

I have a rank formula that counts a variable range for evaluation. However the starting point is a fixed cell reference which is fixed to specific column. I want it to be variable based on find & offset functions as the excel sheet changes & therefore the range in the formula needs to move.

In below how do I change that code so that the starting cell & columns are complete variables?

Sub dural()
Dim N As Long
N = Cells(Rows.Count, "B").End(xlUp).Row
Range("A2:A" & N).Formula = "=rank(B2,B$2:B" & N & ")"

End Sub

 

[braindiesel]

How is this
Sub dural()
Dim N As Long
Dim myCol As Long

myCol = ActiveCell.Column

N = Cells(Rows.Count, myCol + 1).End(xlUp).Row
Range(Cells(2, myCol), Cells(N, myCol)).FormulaR1C1 = "=RANK(RC[1],RC[1]:R" & N & "C[1])"

End Sub