FormulaArray

[Troy]

Hi!

I have the following code which inserts formula in multiple cells:

RC=RowColumn LC=LastColumn SC=StartColumn

myformula = "= SUM(IF(LEN(R" & RC & "C" & SC & ":R" & RC & "C" & LC &
")=1,IF(LEFT(R" & RC & "C" & SC & ":R" & RC & "C" & LC & ")=" & """" &
Mid(MyText, Counter - 6, 1) & """" & ",8.5,0),0)) + SUM(IF(R" & RC & "C" &
SC & ":R" & RC & "C" & LC & "=" & """" & Mid(MyText, Counter - 6, 1) & "!"""
& ",8,0))" & " & " & """ / """ & " & " & "SUM(IF(LEN(R" & RC & "C" & SC &
":R" & RC & "C" & LC & ">1 ),IF(LEFT(R" & RC & "C" & SC & ":R" & RC & "C" &
LC & ") =" & """" & Mid(MyText, Counter - 6, 1) & """" &
",IF(ISNUMBER(VALUE(MID(R" & RC & "C" & SC & ":R" & RC & "C" & LC &
",2,LEN(R" & RC & "C" & SC & ":R" & RC & "C" & LC & ")-1))),VALUE(MID(R" &
RC & "C" & SC & ":R" & RC & "C" & LC & ",2,LEN(R" & RC & "C" & SC & ":R" &
RC & "C" & LC & ")-1)),0),0)))"

I can insert it OK with the following and manually change it to an Array
Formula:

Range(Cells(RC, LC + Counter), Cells(RC, LC + Counter)).Formula = myformula

However when I use the following I get an error message:
"Runtime error 1004" "Unable to set the FormulaArray property of the Range
class."

Range(Cells(RC, LC + Counter), Cells(RC, LC + Counter)).FormulaArray =
myformula

Any help appreciated

Troy

 

[Dave Peterson]

Without knowing what any of your variables are (myText, RC, LC, RC, SC), I'm
guessing that you got hit by a 255 character limit when working with
ArrayFormulas in VBA (measured in R1C1 reference style, too, IIRC).

http://support.microsoft.com/kb/q192989/
XL97: Run-Time Error Using FormulaArray Property

http://support.microsoft.com/kb/q201354/
XL2000: Run-Time Error Using FormulaArray Property