Finding Array Formulas in VBA
 

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

Finding Array Formulas in VBA
 

EM

Range("A1").HasArray

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

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com


ExcelMonkey wrote:
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

Finding Array Formulas in VBA
 

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

Thanks

"Dick Kusleika" wrote:

EM

Range("A1").HasArray

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

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com


ExcelMonkey wrote:
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

Finding Array Formulas in VBA
 

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

--
HTH

Bob Phillips

"ExcelMonkey" wrote in message
...
What do yo mean by part of an array of formulas? Can you give me an
example
of this?

Thanks

"Dick Kusleika" wrote:

EM

Range("A1").HasArray

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

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com


ExcelMonkey wrote:
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

Finding Array Formulas in VBA
 

I can't figure out what that means!!


"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

"ExcelMonkey" wrote in message
...
What do yo mean by part of an array of formulas? Can you give me an
example
of this?

Thanks

"Dick Kusleika" wrote:

EM

Range("A1").HasArray

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

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com


ExcelMonkey wrote:
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

Finding Array Formulas in VBA
 

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

--
HTH

Bob Phillips

"ExcelMonkey" wrote in message
...
I can't figure out what that means!!


"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

"ExcelMonkey" wrote in message
...
What do yo mean by part of an array of formulas? Can you give me an
example
of this?

Thanks

"Dick Kusleika" wrote:

EM

Range("A1").HasArray

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

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com


ExcelMonkey wrote:
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

Finding Array Formulas in VBA
 

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.
--
HTH...

Jim Thomlinson


"ExcelMonkey" wrote:

I can't figure out what that means!!


"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

"ExcelMonkey" wrote in message
...
What do yo mean by part of an array of formulas? Can you give me an
example
of this?

Thanks

"Dick Kusleika" wrote:

EM

Range("A1").HasArray

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

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com


ExcelMonkey wrote:
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

Finding Array Formulas in VBA
 

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.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

ExcelMonkey wrote:
I can't figure out what that means!!


"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

"ExcelMonkey" wrote in
message ...
What do yo mean by part of an array of formulas? Can you give me
an example of this?

Thanks

"Dick Kusleika" wrote:

EM

Range("A1").HasArray

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

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com


ExcelMonkey wrote:
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