VBA to insert formula that has been defined as a String

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,434
Hello Guys,

I have the following code -
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
The second formula that I am trying to use in the code above is:
Code:
{=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
 
Upvote 0
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 :confused:
 
Upvote 0
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.
 
Upvote 0
Hi Stephen,
I tried this
Code:
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
 
Last edited:
Upvote 0
When I use .Formule instead of .FormulaArray like below
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
        .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.
 
Upvote 0
Any intermediate formulae need to parse correctly. Here's one way you could do this:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top