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]

The second formula that I am trying to use in the code above is:

{=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)),"")}

entered with ctrl+shift+Enter

 

[asad]

I tried recording the macro and then matching the two for any differences. But there is none.
Even when I try to run the recorded macro, it gives the same error

 

[StephenCrump]

With .FormulaArray, you're limited to 255 characters. You can work around this: http://dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

But given that you're using an Index/Match construction, won't .Formula suffice, i.e. you don't need .FormulaArray at all?

 

[asad]

Thanks for the reply Stephen.
I have tried just formula. The code works, but the formula doesn't.
It has to be an array formula to give the desired result.
May be I can cut the formula into two portions.
I will try tomorrow.
Thanks.

 

[asad]

Hi Stephen,
I tried this

Sub Fill2()
Dim TheFormulatwoA As String
Dim TheFormulatwoB As String
    TheFormulatwoA = "=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) & "" - " & "X_X_X"
    TheFormulatwoB = "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 Range("E15")


[COLOR=#ff0000]        .FormulaArray = TheFormulatwoA[/COLOR]
        .Replace "X_X_X", TheFormulatwoB
       
    End With
End Sub

But the code line in red above gets highlighted in yellow.
The number of characters is less than 255 in that part but it is still not allowing me to use the formula.

What is it that I am doing wrong?
Any help would be much appreciated.
Asad

 

[asad]

When I use .Formule instead of .FormulaArray like below

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
        .Range("E15").Formula = TheFormulatwo
       
    End With
End Sub

The code works fine, but I don't get any value in the cell E15. Then, if I go in the cell and press ctrl+shift+enter it gives me the answer straight away.

 

[StephenCrump]

Any intermediate formulae need to parse correctly. Here's one way you could do this:

TheFormulatwoA = "=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)&"" - "" & X_X_X),"""")"
TheFormulatwoB = "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 Range("E15")
    .FormulaArray = TheFormulatwoA
    .Replace "X_X_X", TheFormulatwoB
End With

 

[asad]

Beautiful.
Perfect.
Thanks Stephen.

 

[StephenCrump]

You're welcome. I'm glad it's working for you.