Using Array formula in VBA script

Michael1984DK

New Member
Joined
Apr 30, 2018
Messages
27
Hi. I am trying to do a VBA script that automatically selects today date file in a destination folder and uses this in an array formula.

When typing in the formula in the sheet without VBA this works. (Ctrl + Shift + Enter)

Code:
=IFERROR(INDEX('[Deliveries - 13.07.2018.xls]Deliveries - 13.07.2018'!$C$6:$C$1000;SMALL(IF('[Deliveries - 13.07.2018.xls]Deliveries - 13.07.2018'!$O$6:$O$1000=$AZ7;ROW('[Deliveries - 13.07.2018.xls]Deliveries - 13.07.2018'!$C$6:$C$1000)-MIN(ROW('[Deliveries - 13.07.2018.xls]Deliveries - 13.07.2018'!$C$6:$C$1000))+1);COLUMNS($AZ$7:AZ7)));"")

But when typing in and adjusting to select newest file in location I get error message:

"Unable to set FormulaArray property of the Range class"

My VBA script looks as follow:

Code:
Sub Macro1()
'
'
    Dim D As String, dtOOS As String


    dtOOS = Format(Application.WorksheetFunction.WorkDay(Date, 0), "dd.mm.yyyy") ' returns last workday e.g. 01.13
    D = "O:\My Drive\CS&L Nordic\Ingoing Deliveries - AllinOne\[Deliveries - " & dtOOS & ".xls]Deliveries - " & dtOOS


'
    Range("BA7").Select
    Selection.FormulaArray = "=IFERROR(INDEX('" & D & "'!$C$6:$C$1000,SMALL(IF('" & D & "'!$O$6:$O$1000=$AZ7,ROW('" & D & "'!$C$6:$C$1000)-MIN(ROW('" & D & "'!$C$6:$C$1000))+1),COLUMNS($AZ$7:AZ7))),"")"
End Sub

The path is correct and when doing a MsgBox(D), it shows the correct path and filename + sheet name.

I am unsure what I am missing here since I can't get it to work no matter what I do?

I read about the 255 character limit, but the D string should solve this I assume?

(In my local format settings in Excel, I usually use ; instead of , to split up formulas, FYI, but I have tried both without success - Eg. =LEFT(A1;2) )

I hope someone can help out?

Thank you
 

Michael1984DK

New Member
Joined
Apr 30, 2018
Messages
27
Now I am getting error: "Unable to set the FormulaArray property of the range class"

I tried replacing the ".Replace ActiveSheet.Name, D, xlPart" with ".Replace Activesheet.name, "'" & D & "'", xlPart. No luck though.

Could it be because the "activesheet" is now the just opened wb?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Probably. Try this one:

Code:
Sub Macro1()
'
'
    Dim D As String, dtOOS As String, sFileName As String, sPath As String
    Dim wb As Workbook, ws as Worksheet
    sPath = "O:\My Drive\CS&L Nordic\Ingoing Deliveries - AllinOne\"
    dtOOS = Format(Application.WorksheetFunction.WorkDay(Date, 0), "dd.mm.yyyy") ' returns last workday e.g. 01.13
    sFileName = "Deliveries - " & dtOOS & ".xls"
set ws = activesheet
    D = "'[" & sFileName & "]Deliveries - " & dtOOS & "'"

'
    Set wb = Workbooks.Open(sPath & sFileName)
    With ws.Range("BA7")
        .FormulaArray = "=IFERROR(INDEX('" & ws.Name & "'!$C$6:$C$1000,SMALL(IF('" & ws.Name & "'!$O$6:$O$1000=$AZ7,ROW('" & ws.Name & "'!$C$6:$C$1000)-5),COLUMNS($AZ$7:AZ7))),"""")"
        .Replace ws.Name, D, xlPart
    End With
    wb.Close False
End Sub
 

Michael1984DK

New Member
Joined
Apr 30, 2018
Messages
27
Hi again Rory.

All works now!! Thank you so very much! I adjusted a bit and had it do some other things as well before closing the secondary workbook, so now it does exactly like I want it to. Saves me a lot of time every day! :)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Glad to help. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,094
Messages
5,640,078
Members
417,126
Latest member
Jeffman52

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