VBA to insert formula that has been defined as a String

[asad]

Hello Guys,

I have the following code -

Sub Posted_Roster_Fill()Dim Ar As Range
Dim TheFormulaone As String
Dim TheFormulatwo As String
    TheFormulaone = "=IF(OR($B15=""Vacant"",$B15=""""),"""",INDEX('Timetarget Roster Export'!$B$1:$B$1500,MATCH($C15 & D$3,('Timetarget Roster Export'!$AP$1:$AP$1500)*1&'Timetarget Roster Export'!$D$1:$D$1500,0)))"
    TheFormulatwo = "=IFERROR(IF(OR(D15=""OFF"",D15=""""),"""",LEFT(INDEX('Timetarget Roster Export'!$E$1:$E$1500,MATCH($C15&D$3,'Timetarget Roster Export'!$AP$1:$AP$1500&'Timetarget Roster Export'!$D$1:$D$1500,0)),5)&"" - ""&LEFT(INDEX('Timetarget Roster Export'!$F$1:$F$1500,MATCH($C15&D$3,'Timetarget Roster Export'!$AP$1:$AP$1500&'Timetarget Roster Export'!$D$1:$D$1500,0)),5)),"""")"
    With Sheets("Express - Posted")
 
        .Range("D15").FormulaArray = TheFormulaone
[COLOR=#ff0000]        .Range("E15").FormulaArray = TheFormulatwo[/COLOR]
    End With
End Sub

When I tested with only first formula, the code worked, but when I tested with both formulas (just added second formula string), it gave me the error message and the line in red above got highlighted in yellow. Can someone please let me know what am I doing wrong?

Thanks
Asad

 

[asad]

Hello Stephen,

I can't seem to get it right for similar formula in same code. Can you please tell me what's wrong with the following code? Where did I go wrong with parsing of formula?

    TheFormulaoneA = "=IF(OR($B15=""Vacant"",$B15=""""),"""",INDEX('Timetarget Roster Export'!$B$1:$B$3000,MATCH($C15 & D$3,('Timetarget Roster Export'!$AP$1:$AP$3000)*1&'Timetarget Roster Export'!$D$1:$D$3000,0)),X_X)"
    TheFormulaoneB = "IF(OR((('Leave from TT'!$C$2:$C$500)*1=$C41)*('Leave from TT'!$A$2:$A$500<=D$3)*('Leave from TT'!$B$2:$B$500>=D$3)),""Leave"",""OFF""))"

        Range("D15").FormulaArray = TheFormulaoneA
        Range("D15").Replace "X_X", TheFormulaoneB

What should I use instead of ",X_X" in first line of formula?
As you must have noticed that I have used

  TheFormulaone = "=IF(OR($B15=""Vacant"",$B15=""""),"""",INDEX('Timetarget Roster Export'!$B$1:$B$1500,MATCH($C15 & D$3,('Timetarget Roster Export'!$AP$1:$AP$1500)*1&'Timetarget Roster Export'!$D$1:$D$1500,0)))"

with no issues at all. Why then does it not work when I try to add another bit of formula?

 

[asad]

Don't worry Stephen. Sorry to bother you. Found out that I was missing "Iferror" formula from the formula.