Formula Array VBA won't work and i cannot find the issue

[GeorgeDoors]

This is the code i currently have;

Sub Renewals()


' Renewals Macro


' To add special offers for drivers


' Keyboard Shortcut: Ctrl+Shift+R


Dim FullForm As String
Dim SpecialOffer1 As String
Dim SpecialOffer2 As String
Dim SpecialOffer3 As String
Dim SpecialOffer4 As String


FullForm = "X1_X2_X3_X4()"
SpecialOffer1 = "=IFERROR(Index('P:\Salary Exchange\Renewals\Special Offer Data\[DP 01 Special Offers.xlsx]Sheet1'!$D$2:$D$235,"
SpecialOffer2 = "SMALL(IF(VLOOKUP($A2,'P:\Salary Exchange\Renewals\Special Offer Data\[Customer List.xlsx]Sheet2'!$A$1:$B$39,2,0)='P:\Salary Exchange\Renewals\Special Offer Data\[DP 01 Special Offers.xlsx]Sheet1'!$A$2:$A$235,"
SpecialOffer3 = "ROW('P:\Salary Exchange\Renewals\Special Offer Data\[DP 01 Special Offers.xlsx]Sheet1'!$A$2:$A$235)-ROW('P:\Salary Exchange\Renewals\Special Offer Data\[DP 01 Special Offers.xlsx]Sheet1'!$A$2)+1),"
SpecialOffer4 = "COLUMN('P:\Salary Exchange\Renewals\Special Offer Data\[DP 01 Special Offers.xlsx]Sheet1'!A1))),"")"


Columns("W:AG").Select
Selection.NumberFormat = "General"
Range("W2").FormulaArray = FullForm
Range("W2").Replace What:="X1_", Replacement:=SpecialOffer1
Range("W2").Replace What:="X2_", Replacement:=SpecialOffer2
Range("W2").Replace What:="X3_", Replacement:=SpecialOffer3
Range("W2").Replace What:="X4()", Replacement:=SpecialOffer4
Range("W2").Select

but when this is run the result is;
X1_SMALL(IF(VLOOKUP($A2,'P:\Salary Exchange\Renewals\Special Offer Data\[Customer List.xlsx]Sheet2'!$A$1:$B$39,2,0)='P:\Salary Exchange\Renewals\Special Offer Data\[DP 01 Special Offers.xlsx]Sheet1'!$A$2:$A$235,ROW('P:\Salary Exchange\Renewals\Special Offer Data\[DP 01 Special Offers.xlsx]Sheet1'!$A$2:$A$235)-ROW('P:\Salary Exchange\Renewals\Special Offer Data\[DP 01 Special Offers.xlsx]Sheet1'!$A$2)+1),COLUMN('P:\Salary Exchange\Renewals\Special Offer Data\[DP 01 Special Offers.xlsx]Sheet1'!A1))),")

If i take the "=" out of the formula, the full statement is entered but without the equals meaning the formula does not work.

Please help

 

[baitmaster]

An unusual approach but I guess you're doing it this way for a reason

first question, why are you not writing the formula in one go? Why are you writing it in 4 sections and then joining them together a bit at a time?

Second point, assuming you do want to do it this way, I note that it fails on SpecialOffer1. The difference between this and the others is that it contains the = character. You could try adding the = to FullForm and removing it from SpecialOffer1

If you're creating the same formula every time, you might consider locating an actual formula elsewhere in your file by simply copying and pasting the correct version to an unused location, and then copying it back when needed. You could also consider storing it as a named formula, which works like a named range but contains a formula instead of a cell reference

 

[GeorgeDoors]

The formula array will only allow a maximum of 255 characters, so i have broken it down into the 4 sections for it to work. I have tried pasting the formula from another location but this does not work as the string is over 255 characters.
I have tried to put the "=" in the FullForm, but this only returns =X1_X2_X3_X4() as the array.

 

[baitmaster]

you could try putting EQUALS in first i.e. SpecialOffer1 = "EQUALSIFERROR(Index('P:\Salary Exchange\Renewals\Special Offer Data\[DP 01 Special Offers.xlsx]Sheet1'!$D$2:$D$235," and then swapping EQUALS with = later

For me the solution would be to import the data from the source workbook into this workbook and then perform the calculation on that imported data instead. I'm not a fan of linked workbooks as you lose control of the data being used. In this case you'd benefit from simplifying your equation too