Newb needs help - Separating Long Array Formula

  • 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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!