VBA Copy / Paste array formula with relative references

[davey4444]

Hopefully the title explains this OK, I am using the following code to effectively copy and paste my array formula.

Dim CopyFrom1 As Range
Dim Lastrow As Long

Lastrow = Range("A" & Rows.Count).End(xlUp).Row
Set CopyFrom1 = ActiveSheet.Range("C3")
ActiveSheet.Range("C9").Resize(Lastrow).FormulaArray = CopyFrom1.FormulaArray

The array formula is -

=SUMPRODUCT(INDEX(Named_Range_1,MATCH(Upload!$A3,Named_Range_2,0),INDIRECT("1:43")),INDEX(Named_Range_3,MATCH(Upload!$B3,Named_Range_4,0),INDIRECT("1:43")))

How can I get the $A3 and $B3 to reference relative cells?

Thanks in advance.

 

[Worf]

See if this produces the expected result:

Sub Relative()
Dim CopyFrom1 As Range, Lastrow&, s$
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
s = "=SUMPRODUCT(INDEX(Named_Range_1,MATCH(Upload!A3,Named_Range_2,0)," & _
"INDIRECT(""1:43"")),INDEX(Named_Range_3,MATCH(Upload!B3,Named_Range_4,0),INDIRECT(""1:43"")))"
ActiveSheet.Range("c3").FormulaArray = s
Set CopyFrom1 = ActiveSheet.Range("C3")
ActiveSheet.Range("C9").Resize(Lastrow).FormulaArray = CopyFrom1.FormulaArray
End Sub