Search For Array Formulas by Cell Reference

Dear All,

Am in need to find every array formula cell references in a large workbook.

Seems the "find tab does not recognize the { in an array formula.

Does any one have any suggestion on how to find all array formulas by cell reference?

Thanks and regards John

This will return you TRUE/FALSE for specific cell if it contains array:

Please Login or Register  to view this content.

Similliary you can perform same test for each cell.

Hi Zbor,

Thanks, but not so practical as it would be no different from looking manually at each cell.

Any other suggestions?

Regards John

The point is in .HasArray not Msgbox

And then you can do whatever you want with it.
Highligh,
extract to some sheet
etc

Hi Sixthsense,

Thanks for your response.

Let me set up your VBA in a simple spreadsheet and test it.

Regards John

Hi,

Definitely works, thanks I could have never written a macro to do that.

Will try and adapt it to look at every worksheet in the workbook.

Kindest regards John

Hi SixthSense,

Much more elegant than I came up with.

Will go with your code

Thanks you make me humble, John

Sixthsense
I think you need to 'reset' colors at the start...
In case user remove formula at some point

Sixthsense
I think you need to 'reset' colors at the start...
In case user remove formula at some point

Hi,

Seems the multiple worksheet code above is not working beyond the first worksheet, not sure why.

The code I ended up with noted below in case anyone is interested.

Once again thanks for all the help, regards John

Please Login or Register  to view this content.

To fix code in post #8 change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Alf