VBA is changing the Cell references of a Array formula causing it to malfunction?

[Event2020]

Windows 10
Excel 2019

I am using VBA to enter an array formula and for some reason it is altering the cell references and when it copies the formula down a column it does not auto increment the cell references in the same way that copying or dragging down would do when the same formula is entered manually using Ctrl Shift Enter does.

To keep this post clean and to the point I have not posted my whole VBA module as it is rather large and all other parts of it perform as expected but if it would help then please tell me and I will post it in its intierity.

This is the array formula that works perfectly if entered manually using CSE as described above.

=MID(FolderDataImport!A1,FIND("-",FolderDataImport!A1)+2,MIN(FIND({"[","("},FolderDataImport!A1))-FIND("-",FolderDataImport!A1)-3)

When copied or dragged down the Cell reference is auto incrimented like:

FolderDataImport!A1
FolderDataImport!A2
FolderDataImport!A3

and so on as long as there is data in the cells in FolderDataImport!A column.

To convert it to use in VBA I used macro recorder while I entered the array formula which then turned the formula into:

Selection.FormulaArray = "=MID(FolderDataImport!R[-7]C[-2],FIND(""-"",FolderDataImport!R[-7]C[-2])+2,MIN(FIND({""["",""(""},FolderDataImport!R[-7]C[-2]))-FIND(""-"",FolderDataImport!R[-7]C[-2])-3)"

After running the VBA sub the array it has actually entered iinto the cells in the sheet is

{=MID(FolderDataImport!A1048571,FIND("-",FolderDataImport!A1048571)+2,MIN(FIND({"[","("},FolderDataImport!A1048571))-FIND("-",FolderDataImport!A1048571)-3)}

as you can see it has added "048571" to the end of each cell refernce and it enters this all the way down the column with out incrimenting the reference causing a #VALUE error.

Array formula's are rather over my head so if anyone can tell me what is going wrong I would be very grateful.

 

[Event2020]

Thank you to everyone for your kind time and suggestions
all of which are very much appreciated.

I will now test all three with real data and see which one fits my needs before marking which one I use as the solution.

Thanks everyone.