Insert Array formula in Excel VBA

Question

I recorded the array formula in order to put in VBA. Here is what I have after the recording. However, when I run the Macro, it just doesn't work.

Will it be because of the negative sign?

From Macro

Range("D3").FormulaArray = "=IFERROR(INDEX('RMS 
Maint'!R1C[-2]:R3542C[-2],SMALL(IF(('RMS Maint'!R2C27:R3542C27=R1C2)*('RMS 
Maint'!R2C13:R3542C13=R2C4)*('RMS Maint'!R2C21:R3542C21=""Late"")*ROW('RMS 
Maint'!R2C1:R3542C1)=0,"""",('RMS Maint'!R2C27:R3542C27=R1C2)*('RMS 
Maint'!R2C13:R3542C13=R2C4)*('RMS Maint'!R2C21:R3542C21=""Late"")*ROW('RMS 
Maint'!R2C1:R3542C1)),ROW('RMS Maint'!R[-2]:R[3538])),1),"""")"

From Excel formula

=IFERROR(INDEX('RMS Maint'!C$1:C$3542,SMALL(IF(('RMS 
Maint'!$AA$2:$AA$3542=$B$1)*('RMS Maint'!$M$2:$M$3542=$D$2)*('RMS 
Maint'!$U$2:$U$3542="Late")*ROW('RMS Maint'!$A$2:$A$3542)=0,"",('RMS 
Maint'!$AA$2:$AA$3542=$B$1)*('RMS Maint'!$M$2:$M$3542=$D$2)*('RMS 
Maint'!$U$2:$U$3542="Late")*ROW('RMS Maint'!$A$2:$A$3542)),ROW('RMS 
Maint'!1:3541)),1),"")

The error is 1004 - Unable to set the FormulaArray property of the Range class

I'm sorry for the code format. It looked terrible.

The limit of a formula array insert is 255 characters with VBA — Scott Craner
– Scott Craner, Commented Apr 25, 2018 at 20:28
which means I would need to create ranges for the cells.Thank you! May I ask where you got that info. I want to check it out. — Oliver Wu
– Oliver Wu, Commented Apr 25, 2018 at 20:39

Subodh Tiwari sktneer · Accepted Answer · 2018-04-25 20:55:59Z

2

Or you may break the long formula into few parts and replace it in the end with the actual formula like below...

Dim LogicalTest As String, FalseValue As String

LogicalTest = "('RMS Maint'!$AA$2:$AA$3542=$B$1)*('RMS Maint'!$M$2:$M$3542=$D$2)*('RMS Maint'!$U$2:$U$3542=""Late"")*ROW('RMS Maint'!$A$2:$A$3542)=0"
FalseValue = "('RMS Maint'!$AA$2:$AA$3542=$B$1)*('RMS Maint'!$M$2:$M$3542=$D$2)*('RMS Maint'!$U$2:$U$3542=""Late"")*ROW('RMS Maint'!$A$2:$A$3542)"

Range("D3").FormulaArray = "=IFERROR(INDEX('RMS Maint'!C$1:C$3542,SMALL(IF(""LogicalTest"","""",""FalseValue""),ROW('RMS Maint'!1:3541)),1),"""")"

Range("D3").Replace """LogicalTest""", LogicalTest, LookAt:=xlPart
Range("D3").Replace """FalseValue""", FalseValue, LookAt:=xlPart

answered Apr 25, 2018 at 20:55

Subodh Tiwari sktneer

9,9862 gold badges20 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Oliver Wu Over a year ago

That's another elegant way to do that! Awesome

Subodh Tiwari sktneer Over a year ago

@OliverBird Thanks! Any automation is incomplete if you are forced to ask the user to take some manual actions. So to me, actually it doesn't make any sense entering the formula as a regular formula and then ask the user to reenter it as an Array Formula. :)

Gary's Student · Accepted Answer · 2018-04-25 20:44:26Z

1

There is an option of last resort: Use a human.

Have the macro place the formula into the cell as a String and have the user complete the process:

Sub pinocchio()
    Range("D3") = "'=1+2"
    MsgBox "User:  Make this into a real array formula"
End Sub

answered Apr 25, 2018 at 20:44

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

1 Comment

Oliver Wu Over a year ago

I SEE! That's creative. Thank you!

Collectives™ on Stack Overflow

Insert Array formula in Excel VBA

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related