Using Array formula in VBA script

[Michael1984DK]

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)

=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:

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]

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?

 

[RoryA]

Probably. Try this one:

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]

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]

Glad to help.