Dynamic Range in Formulas VBA

Hi,

How can I make the range in a formula to work with a dynamic range?

* 'R2C5:R640C5' & 'R2C5:R45C5' (shown below) is a variable range on the spreadsheet and not a fixed one.
* Attached example only has what's applicable to the questions.

1. COUNTIF: Used to count the number of repetitions on each cell value (Item#). That'll be Column A in the attached example.

ActiveCell.FormulaR1C1 = "=COUNTIF(R2C5:R640C5,RC[-6])"
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K" & Range("C" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

2. INDEX & MATCH: Used to lookup and match 'Item#' and return 'HA#'. That'll be lookup Column A and match with Column B in Sheet#2, then return Column A from Sheet#2 to Column C.

Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('HA Data'!R2C4:R45C4,MATCH('Sample Data'!RC[-7],'HA Data'!R2C5:R45C5,0),1)"
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L" & Range("C" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

Hopefully this makes sense.