excel formulaarray

Question

hi why do i get the runtime error 13: type mismatch error while running the following code

 Application.Goto Reference:="R1C1:R232C221"
 Selection.FormulaArray = "=ROUND(a(),0)"
 Selection.Replace What:="a()", Replacement:="IF(IF(Sheet4!A1:HM232+Sheet5!A1:HM232=2,0," & _
"Sheet4!A1:HM232+Sheet5!A1:HM232)+IF(Sheet4!A1:HM232+Sheet5!A1:HM232=2,0," & _
"Sheet4!A1:HM232+Sheet5!A1:HM232)=2,0,IF(Sheet4!A1:HM232+Sheet5!A1:HM232=2,0," & _
"Sheet4!A1:HM232+Sheet5!A1:HM232)+IF(Sheet4!A1:HM232+Sheet5!A1:HM232=2,0,Sheet4!A1:HM232+Sheet5!A1:HM232))", LookAt _
    :=xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
    ReplaceFormat:=False
Range("I9").Select

1) i know that formulaarray should be in R1C1 style... but A1 style is not required, it also works without any problems in A1 style

  http://msdn.microsoft.com/en-us/library/bb208529.aspx

2) i found this way of writing from

  http://www.dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

Answer 1

I believe the "replacement" argument is too long. You need to keep that string under 255 characters. Put more of the formula into the Selection.FormulaArray line so that the replacement is less than 255 characters.

Answer 2

Let's analyze your Replacement:

Replacement:="IF(IF(Sheet4!A1:HM232+Sheet5!A1:HM232=2,0," & _
"Sheet4!A1:HM232+Sheet5!A1:HM232)+IF(Sheet4!A1:HM232+Sheet5!A1:HM232=2,0," & _
"Sheet4!A1:HM232+Sheet5!A1:HM232)=2,0,IF(Sheet4!A1:HM232+Sheet5!A1:HM232=2,0," & _
"Sheet4!A1:HM232+Sheet5!A1:HM232)+IF(Sheet4!A1:HM232+Sheet5!A1:HM232=2,0,Sheet4!A1:HM232+Sheet5!A1:HM232))"

(1) change Sheet4!A1:HM232+Sheet5!A1:HM232 to X:

Replacement:="IF(IF(X=2,0," & _
"X)+IF(X=2,0," & _
"X)=2,0,IF(X=2,0," & _
"X)+IF(X=2,0,X))"

(2) glue the broken pieces back together:

Replacement:="IF(IF(X=2,0,X)+IF(X=2,0,X)=2,0,IF(X=2,0,X)+IF(X=2,0,X))"

(3) change IF(X=2,0,X) to Y:

Replacement:="IF(Y+Y=2,0,Y+Y)"

(4) some evaluation:

• If X is 2, Y is 0, and the result is 0.
• If X is 1, Y is 1, and the result is 0.
• If X is anything else, Y is X, and the result is 2*X.

(5) so the formula is equivalent to:

Replacement:="IF(OR(X=2,X=1),0,2*X)"

(6) so the next step would be to replace X (step 1 in reverse) ...

I'll just leave you with a question or two: How did it become so messy? Have you heard of "DRY" (Don't Repeat Yourself)?