Variables in Formula Array not returning the stored values

[AmaChama]

Hi everyone,

I've been working on a Formula Array code for the Index Sumproduct function to do a multi-criteria lookup. In the Formula Array code, RE, RA, i, RISKC, GENDER, FACE_BAND, PRODUCT, and IA are all variables. I've placed quotes around them accordingly and executed the code, but, for some reason, only the variables RE, RA, i, and IA return the correct value. The other variables return their name in text. I.e., the RISKC variable returns "RISKC", GENDER returns "GENDER."

I've printed the variables prior to running this portion of the code, and they all return the appropriate values. In the Formula Array code, they aren't.

Here is the part of the code I've been working on:

        If PRODUCT = "WL" Or PRODUCT = "UL" Then
            For i = 1 To 8
                For d = 10 * i - 9 To 10 * i
                    Sheets(RE & " Calculator").Cells(d + 1, 10).FormulaArray = "=INDEX(" & RE & "_Rates, SUMPRODUCT((" & RA & "_DUR = " & i & ")*(" & RA & "_RISKC =" & RISKC & ")*(" & RA & "_GNDR =" & GENDER & ")*(" & RA & "_FACE =" & FACE_BAND & ")*(" & RA & "_PROD =" & PRODUCT & ")*(ROW(" & RA & "_DUR) - 4)), " & IA & ")"
                Next d
            Next i

It returns this in Excel:

=INDEX(MUNICH_Rates, SUMPRODUCT((M_DUR = 1)*(M_RISKC = RISKC)*(M_GNDR = GENDER)*(M_FACE = FACE_BAND)*(M_PROD = Product)*(ROW(M_DUR) - 4)), 4)

Thanks so much in advance!
Amy