Insert Array formula in Excel VBA

1
votes

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.

2
vbaexcel
The limit of a formula array insert is 255 characters with VBA - Scott Craner
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 Bird

2 Answers

2
votes

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
1
votes

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