VBA to insert formula that has been defined as a String

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,428
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,428
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
 

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,428
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:
 

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,428

ADVERTISEMENT

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

Well-known Member
Joined
Sep 9, 2008
Messages
1,428
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:

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,428

ADVERTISEMENT

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.
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,043
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,567
Messages
5,596,906
Members
414,110
Latest member
docops

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
Top