Solved: Formula with ranges based on variables

[tdyel]

I am trying to set a formula in a cell based on a range that will be determined by a variable. This is how the formula should look in the cell:

=RANK(H6, $H$6:$H$42)

However, the end of the range (cell H42) needs to be determined by a variable, meaning the variable will determine the row, 42 in this case.
I tried using the FormulaR1C1 property with no luck. This is how I thought it should look:

=RANK(Cells(6, 8), Range(Cells(6, 8), Cells(lastRow, 8)))
where lastRow is the ending row in the desired range.

It didn’t work this way and I realize this isn’t the proper syntax for R1C1 notation, but I don’t know how else to work it. Please help.

[figment]

are you trying to do this on the sheet or in code?

[tdyel]

I was trying to do it in code, but I actually figured it out already.
Thanks for you help. This is what I ended up using:

Range("A6").Formula = "=RANK(H6,$H$6:$H$" & break - 1 & ")"