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

 

[RoryA]

Using D will not solve the 255 character limit - I have not checked to see if you are actually hitting that. However you do need to replace the "" as the last argument to IFERROR with """" since you are using literal quotes in a quoted string.

 

[Michael1984DK]

Wow, thank you for fast reply.

I replaced the "" with """":

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

Still getting the error however.

From where does the 255 start counting? Selection.FormulaArray to the last " ?

If it copies it the full path ontop of the formula, this is 205 + 4*118 = 677 characters long.. Is there any way to shorten this down to <255?

 

[RoryA]

It counts from the = sign at the start of the formula. You could shorten it a lot by opening the file first so you don't have to include the path, and replace this part:

-MIN(ROW('" & D & "'!$C$6:$C$1000))+1

with just:

-5

You could also use the active sheet in the formula to start with then replace it:

    With Range("BA7")
.FormulaArray = "=IFERROR(INDEX('" & activesheet.name & "'!$C$6:$C$1000,SMALL(IF('" & activesheet.name & "'!$O$6:$O$1000=$AZ7,ROW('" & activesheet.name & "'!$C$6:$C$1000)-5),COLUMNS($AZ$7:AZ7))),"""")"
.Replace Activesheet.name, D, xlPart
End With

 

[Michael1984DK]

Hi again.

Ahh so because = -MIN(ROW('" & D & "'!$C$6:$C$1000))+1 = -6 (C6) + 1 = -5.. makes sense.. *Facepalm at myself*

I am not sure I follow the last part though..

* You left out Selection and went straight to .FormulaArray (can I write it like that instead)?

* About the opening the file ahead.. Can I make the script to this for me then? (should be fairly easy with the path and dynamic filename already defined?)

* So regaring the activesheet.. would this be in the file from which I am running the script or from the file where I collect the data from? I am still kind of a newbie in VBA compared to you guys in here, so I am a bit confused. You write '" & activesheet.name & "'! and then in the end you replace this with D.. Should I literally write it like this?

Sorry for asking (I'm sure) stupid questions.

 

[RoryA]

You almost never need to select anything in VBA to use it. Try the code exactly as posted, and see what happens - i.e.

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


'
    With Range("BA7")
      .FormulaArray = "=IFERROR(INDEX('" & activesheet.name & "'!$C$6:$C$1000,SMALL(IF('" & activesheet.name & "'!$O$6:$O$1000=$AZ7,ROW('" & activesheet.name & "'!$C$6:$C$1000)-5),COLUMNS($AZ$7:AZ7))),"""")"
   .Replace Activesheet.name, D, xlPart
End With
End Sub

 

[Michael1984DK]

Good Morning Rory.

I tried putting it in as is, and it kind of worked. No error messages or anything. The only thing is, that it didnt do the "replace" in the formula in the end, so every time the formula says activesheet.name, it just refers to the active sheet that the formula is working in and not the external file from where it should pull the data.

The sheet in this "main" file is called DKH1_Straight

This is the formula in the macro destination cell after running:

{=IFERROR(INDEX(DKH1_Straight!$C$6:$C$1000;SMALL(IF(DKH1_Straight!$O$6:$O$1000=$AZ7;ROW(DKH1_Straight!$C$6:$C$1000)-5);COLUMNS($AZ$7:AZ7)));"")}

 

[RoryA]

Try changing the Replace line to this:

.Replace Activesheet.name, "'" & D & "'", xlPart

 

[Michael1984DK]

It works!!!! :D This is great! Monday morning just got a whole lot better.

Last thing now is how I get the script to open the D file before updating and then closing it again in the end before End sub? (It tells me to open the file before updates can happen).

1000 thanks so far!! I was almost ready to give up last week.

 

[RoryA]

Try this:

Sub Macro1()
'
'
    Dim D As String, dtOOS As String, sFileName As String, sPath As String
    Dim wb As Workbook
    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"

    D = "'[" & sFileName & "]Deliveries - " & dtOOS & "'"

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