asked on

How can I make array formulae shorter in a macro?

Can I reduce the length of "Array Formulae" to work in a macro or split into smaller portions. I have several formulae in the attach file that have a length longer than 255 characters as a result they fail. Could someone assist by improving this code. At this time, I'm only working with row (5) as a test with the rest of the rows having formulae in the colored cells that return the results required.

Any assistence would be appreciated.

THANKS
ReportTest-B.xlsm

Norie

Which worksheet are these formulas to go on?

John Smith

ASKER

The formulas are to be placed in worksheet "Nambucca_October 2011" in row (5) in the colored cells. If look in row (6) you'll see working formula that are being used as a sample.
Sorry, if I wasn't clear on this.

Thanks

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Smith

ASKER

Barry your the man.

Your code works perfectly, many Thanks.
= IFERROR(1/(1/INDEX(w
In the above portion of codes "1/(1/INDEX" can you tell me what this means in plan english?

I appreciate the efforts from this site.

barry houdini

We want to force an error when the INDEX formula returns zero, so if you divide 1 by the result of the INDEX formula then you get a #DIV/0! error where the result is zero, but if, for example, the result of INDEX is 10 then 1/(1/10) = 10 so if INDEX returns any other number the result will be unchanged.

IFERROR will then convert the #DIV/0! errors to blanks

regards, barry