Macro with Array Function Problems
 

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?

Macro with Array Function Problems
 

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.

Macro with Array Function Problems
 

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)}


"Harlan Grove" wrote:

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.

Macro with Array Function Problems
 

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.

"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?

Macro with Array Function Problems
 

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?

"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?

Macro with Array Function Problems
 

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
insertnamedefine 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 insertnamedefine then you can refer to it as

=Formula1


--

Regards,

Peo Sjoblom

"Brett" wrote in message
...
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?

"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?