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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,487
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Apr 30, 2018
Messages
27
Wow, thank you for fast reply. :)

I replaced the "" with """":

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

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,487
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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:

Code:
    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
 
Last edited:

Michael1984DK

New Member
Joined
Apr 30, 2018
Messages
27

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,487
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You almost never need to select anything in VBA to use it. Try the code exactly as posted, and see what happens - i.e.

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


'
    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
 
Last edited:

Michael1984DK

New Member
Joined
Apr 30, 2018
Messages
27

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,487
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Try changing the Replace line to this:

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

Michael1984DK

New Member
Joined
Apr 30, 2018
Messages
27
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

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

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

Watch MrExcel Video

Forum statistics

Threads
1,108,619
Messages
5,523,940
Members
409,543
Latest member
LaMaqu1na

This Week's Hot Topics

Top