I'm working with a long array formula, and I'm trying to break it apart to get it to work as part of a longer macro created manually (updating the original macro via code). I'm getting a syntax error (like I said, I'm still new to this). Would very much appreciate some guidance. (Radio Call Signs in CAPS denote named lists). Thank you!
Sub ArrayReplacementMacro()
Dim theFormulaPart1 As String
Dim theFormulaPart2 As String
Dim theFormulaPart3 As String
Dim theFormulaPart4 As String
theFormulaPart1 = "("("&INDEX(ALPHA,MATCH(1,(RC2=BRAVO)*(R8C=CHARLIE),0))" & Chr(10) & "&" & Chr(10) & """,""" & Chr(10) & "&" & Chr(10) & xxx"
theFormulaPart2 = "IF(INDEX(DELTA,MATCH(1,(RC2=BRAVO)*(R8C=CHARLIE),0))>0,"YES","NO")xxy"
theFormulaPart3 = "&")"&""&"-"&""&"["&INDEX(ECHO,MATCH(1,(RC2=FOXTROT)*(AB$787=GILA),0))" & Chr(10) & "&" & Chr(10) & """,""" & Chr(10) & "&" & Chr(10) & xyy"
theFormulaPart4 = "IF(INDEX(HOTEL,MATCH(1,(RC2=FOXTROT)*(R787C28=GILA),0))>0,"YES","NO")&"]")"
With Sheets("Generic Worksheet").Range("AJ1")
.FormulaArray = theFormulaPart1
.Replace "xxx", theFormulaPart2, xlPart
.Replace "xxy", theFormulaPart3, xlPart
.Replace "xyy", theFormulaPart4, xlPart
End With
End Sub