Referencing cells in formulas using a defined variable

[fox1589]

Hi,

I have the following code
[code]

Dim LastRow As Long
Dim a As Long
..
..
..
a = [SUMPRODUCT(1/COUNTIF(Sheet1!O2:O5065,Sheet1!O2:O5065))]

Worksheets("Sheet2").Range("D2").FormulaArray = "=IFERROR(INDEX(Sheet1!$O$2:$O$5065, MATCH(0,COUNTIF($D$1:D1, Sheet1!$O$2:$O$5065), 0)),"""")"

[\code]

the variable, LastRow, is the value of last row in my spreadsheet, which is 5065. I was wondering how to replace the 5065 in each of these lines with a reference to the variable, LastRow

Thanks!!

[YasserKhalil]

Try

Worksheets("Sheet2").Range("D2").FormulaArray = "=IFERROR(INDEX(Sheet1!$O$2:$O$" & LastRow & ", MATCH(0,COUNTIF($D$1:D1, Sheet1!$O$2:$O$" & LastRow & "), 0)),"""")"

[fox1589]

Thank you so much!!

Just, how would the replacement work for the first line (a=..)? Because I tried the recommendation you had for the second line, but it didnt work

[YasserKhalil]

Look at the red font to get it easily

Worksheets("Sheet2").Range("D2").FormulaArray = "=IFERROR(INDEX(Sheet1!$O$2:$O$" & LastRow & ", MATCH(0,COUNTIF($D$1:D1, Sheet1!$O$2:$O$" & LastRow & "), 0)),"""")"