formulaarray

Hi guys, Wondering if anyone can help me. I am getting an error: "Unable to set the FormulaArray property of the range class" when I try to run the following code: Range("AK3").FormulaArray =...

Hello all, I am in the process of writing a good-sized macro to automate the production of a particular report, so that all of the additional columns, pivot tables and other analysis drawing from data brought over from another report on a monthly basis can effortlessly be set up without the...

I was in the process of figuring out some trick to make range's FormulaArray property accept strings >255 chars. Along the way in the interwebs, I came across a link which stated one can indirectly accomplish that by setting a named constant with a value >255 chars (this is apparently possible...

Hello, Im trying to make something that will manipulate data consistently across multiple workbooks. I am having trouble with the 255 character limit of the FormulaArray function (I tried the .replace workaround and I'm not sure if I did it correctly) and when I'm not getting that error I'm...

To get around the limitation that Range.FormulaArray cannot accept a formula more than 255 chars in length, I use this work around: long_formula_part1 = "=ZZZ(""a"",""b"",""c"",""somekey=tick,tock,jog"",dummy" long_formula_part2 = ",""e"",""f"",""g"")" to_replace = ",dummy" act_formula = "ABC"...

I have some vba code that places a UDF formula on a particular Range's FormulaArray property. Problem is the formula length can sometimes exceed 255 chars causing the code to fail. I posted this question at the MSDN forums here...

I use the formula array property of a range to display 2D variant data into individual cells in Excel. So I have: Sub AdjustRange(rows as Integer, cols as Integer) Dim r as Range set r = Range("A3").Resize(rows, cols) r.FormulaArray = "=SomeWorkSheetFunctionThatReturnsAMatrix()" End...

I seem to be stuck trying to use multiple variables in an array statement. =SUM(IF(Hours!$A$2:$A$13189=HR!$A5,IF(OR(Hours!$B$2:$B$13189="ENG",Hours!$B$2:$B$13189="SNRENG"),Hours!$D$2:$D$13189,),)) Right now it sums everything if the first expression passes, so if the Hours column A matches...

I need to pare down the number of calculations on the worksheet because it is slow. I'm trying to convert the following formula to a VBA code. {=SUM(IF(I:I>=AI3,IF(I:I<AI4,1,0),0))} where, col1 = "I" col8 = "AI" col11 = "AL" i = 3 the current cell = AL3 Does anyone know how this could be...

Greetings My FormulaArray is too long - the dreaded +255 characters limit coming into effect. I have tried two different workarounds with mixed results. First I tried splitting the formula (a long If, ISERROR, MATCH, INDEX function with multiple criteria) into two parts, declaring each...

Hi, I have this working arrayformula in excel (only one sheet in the .xls): {=SUM(IF((C5:C25<3);G5:G25;0))-SUM(IF((C5:C25<3)+IF(ISNUMBER(SEARCH("*flat*";I5:I25;1));1;0)=2;G5:G25;0))} What I want to do now is to execute this array formula using VBA and store the result in cell A1. I tried...

anyone tell me how ot reference a range using r1c1 and the sheetname, my sheet name is 0 I have "=ABS(Worksheets(0).Cells("R2509C3:R2717C41"))" it works fine as "=ABS("R2509C3:R2717C41")" but obviously references the current sheet, and not sheet "0"

I'm trying to assign (in excel 2003, sp3) a string to the FormulaArray property of a range. The length of the string is 305, causing a crash because it is maxed over 255 characters (the error and cause is documentated poorly at http://support.microsoft.com/kb/213181, I say poorly because at the...