Hi Experts,

Been trying to figure out this for half a day already. 

FormulaPart1 = "=IF(RC19="""","""",IF(AND(RC[-9]=""Rerouted to Creator"",OR(""OSB_USR""=INDEX(R2C3:R39C3,MATCH(1,(RC2=R2C2:R39C2)*(MIN(RC8:R39C8)=R2C8:R39C8),0)),RC21=INDEX(R2C6:R39C6,MATCH(1,(RC2=R2C2:R39C2)*(MIN(RC8:R39C8)=R2C8:R39C8),0)))),XXXXX, YYYYY))"
FormulaPart2 = "INDEX(R2C7:R39C7,MATCH(1,(RC2=R2C2:R39C2)*(MIN(RC8:R39C8)=R2C8:R39C8),0)),IF(ISNA(MATCH(RC[11],UserID,0)),IF(LEFT(RC21,2)=""SA"",""AEGIS"",IF(OR(LEFT(RC21,4)=""JCVC"""
FormulaPart3 = "LEFT(RC21,2)=""VC""),""VADS"",IF(LEFT(RC21,1)=""C"",""BlueCube"",IF(LEFT(RC21,2)=""V0"",""Other V"",""Celcom"")))),""SA"")))"

Application.ReferenceStyle = xlR1C1
  
With ActiveSheet.Range("AF2")
.FormulaArray = FormulaPart1
.Replace "XXXXX", FormulaPart2, lookat:=xlPart
.Replace "YYYYY))", FormulaPart3, lookat:=xlPart
End With

Application.ReferenceStyle = xlA1
Why when I run the macro, the output came out like below?:-
{=IF($S2="","",IF(AND(W2="Rerouted to Creator",OR("OSB_USR"=INDEX($C$2:$C$39,MATCH(1,($B2=$B$2:$B$39)*(MIN($H2:$H$39)=$H$2:$H$39),0)),$U2=INDEX($F$2:$F$39,MATCH(1,($B2=$B$2:$B$39)*(MIN($H2:$H$39)=$H$2:$H$39),0)))),XXXXX, YYYYY))}

Seems like it doesn't recognize the XXXXX and the YYYYY as strings that needs to be replaced...
I've done this several times for other long formulas and it works, although it could be such a painful thing to do... but I just don't get it why this one doesn't work?

Really appreciate if anyone out there can correct my formulas..

Thanks in advance.
DZ