Home |
Search |
Today's Posts |
#1
|
|||
|
|||
FormulaArray
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 |
#2
|
|||
|
|||
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 Troy wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|