• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

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
0
user2073
Asked:
user2073
  • 2
  • 2
1 Solution
 
NorieCommented:
Which worksheet are these formulas to go on?
0
 
user2073Author Commented:
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
0
 
barry houdiniCommented:
In your longest formulas you are repeating the formula twice just to make a zero into a blank, do you really need to do that? If that's just for display purposes you can simply format the cells to display zeroes as blanks, alternatively try using this version for the first problem formula

"=IFERROR(1/(1/INDEX(ws1!R4C1:R10000C17,MATCH(RC[-14]&TEXT(EDATE(R2C2,-1),""mmmmyyyy""),ws1!R4C4:R10000C4 & ws1!R4C3:R10000C3 & ws1!R4C2:R10000C2,0), MATCH(""Data1"", ws1!R2C1:R2C17,0))),"""")"

Open in new window


Note I changed the date part, if your R2C2 date is January you'll get the wrong year, so I changed that......

regards, barry
0
 
user2073Author Commented:
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.
0
 
barry houdiniCommented:
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
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now