Macro with Array Function Problems

[Guest]

I'm in the process of making a very large macro to format a report and
identify errors. To do this, I have some gigantic array formulas, and they
seem to be causing the macro problems. When I recorded the macro, a pop-up
box would come up saying "Unable to Record" when I ctrl+shift+entered the
formula (the formula does work when not trying to record macro). Now, when I
try to run the macro it bombs my system. Is there a size limitation for
array formulas in macros? Any suggestions?

 

[Harlan Grove]

Brett wrote...

I'm in the process of making a very large macro to format a report and
identify errors. To do this, I have some gigantic array formulas, and they
seem to be causing the macro problems. When I recorded the macro, a pop-up
box would come up saying "Unable to Record" when I ctrl+shift+entered the
formula (the formula does work when not trying to record macro). Now, when I
try to run the macro it bombs my system. Is there a size limitation for
array formulas in macros? Any suggestions?

Only suggestion: provide actual details.

 

[Guest]

For example, this is one of the functions that will not record:

{=IF(ISERROR(INDEX('Raw Data'!$A$2:$T$5000,MATCH(LARGE(IF('Raw
Data'!$A$2:$T$5001=A2,'Raw Data'!$Q$2:$Q$5001),1),'Raw
Data'!$Q$2:$Q$5001,0),14))=FALSE,INDEX('Raw
Data'!$A$2:$T$5000,MATCH(LARGE(IF('Raw Data'!$A$2:$T$5001=A2,'Raw
Data'!$Q$2:$Q$5001),1),'Raw Data'!$Q$2:$Q$5001,0),14),0)}

 

[Guest]

Here's even more detail. The problem is that the first function runs, but
the second one gives an error "Unable to set the FormulaArray property of the
Range Class".

Selection.FormulaArray = _
"=IF(ISERROR(LARGE(IF('Raw Data'!R2C1:R5001C20=RC[-7],'Raw
Data'!R2C17:R5001C17),1))=FALSE,LARGE(IF('Raw Data'!R2C1:R5001C20=RC[-7],'Raw
Data'!R2C17:R5001C17),1),0)"
Selection.AutoFill Destination:=Range("H2:H134")
Range("H2:H134").Select
Range("I2").Select
Sheets("References").Select
Range("I4").Select
Selection.Copy
Sheets("All Data").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.FormulaArray = _
"=IF(ISERROR(INDEX('Raw Data'!$A$2:$T$1000,MATCH(LARGE(IF('Raw
Data'!$A$2:$T$1000=A2,'Raw Data'!$Q$2:$Q$1000),1),'Raw
Data'!$Q$2:$Q$1000,0),14))=FALSE,INDEX('Raw
Data'!$A$2:$T$1000,MATCH(LARGE(IF('Raw Data'!$A$2:$T$1000=A2,'Raw
Data'!$Q$2:$Q$1000),1),'Raw Data'!$Q$2:$Q$1000,0),14),0)"
Selection.AutoFill Destination:=Range("I2:I134")
Range("I2:I134").Select

I apologize for the extranaeous code.

 

[Guest]

Alright, I figured out my problem, but it isn't a solution. The formulas
that are not working are over the 255 character limit. I could possible
split some up, but this would make the spreadsheet look very clunky and
confusing. Does anyone know of any way to get aroung this bug?

 

[Peo Sjoblom]

I don't think a limitation is a bug per se, if the macro doesn't work it is
a bug in your code. However you can name parts of the formula using
insert>name>define which in fact would make your workbook look less clunky.
e.g.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)

will return the sheet name and if you use that in a formula for the sheet
name in multiple places it would make the formula long, now if you give it a
name like formula1 doing insert>name>define then you can refer to it as

=Formula1