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

Event2020

Board Regular
Joined
Jan 6, 2011
Messages
88
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.
VBA Code:
=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:
VBA Code:
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
VBA Code:
{=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

Board Regular
Joined
Jan 6, 2011
Messages
88
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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Watch MrExcel Video

Forum statistics

Threads
1,132,707
Messages
5,654,840
Members
418,156
Latest member
juliapearson

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