Finding Array Formulas in VBA

[Guest]

How do you search a cell for array formulas? Using the "{" as a search term
in Excel's Find does not seem to do it. Is ther a way in VBA to find these?

{=MAX(IF(MONTH(E6:E12)=F14, F6:F12, 0))}

Thanks

 

[Dick Kusleika]

EM

Range("A1").HasArray

will return True if it has an array formula or is part of an array of
formulas.

 

[Guest]

What do yo mean by part of an array of formulas? Can you give me an example
of this?

Thanks

 

[Bob Phillips]

An array formula that is applied to more than one cell.

 

[Guest]

I can't figure out what that means!!

An array formula that is applied to more than one cell.

 

[Bob Phillips]

It means that instead of the formula being applied to just one cell, it is
applied to many.

 

[Guest]

An array is a group of similar items. So in this case E6:E12 is an array and
so is F6:F12. It is not a single value, but rather a group of similar
values. That is what is being detected by the isarray formula.

 

[Dick Kusleika]

EM

In A1:A10, put 1,2,3... 10

Select B1:B10 and type =A1*2 then Control+Shift+Enter. The formula will be
in each cell B1:B10. Now try to delete B5. Honestly, I never use this type
of array formula.