| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Helpme with Forumal Array option
I get the following error while trying to run the Macro, I just started
learing. "Unable to set the formulaArray Property of the Range class - Error" Selection.FormulaArray = "=SUM(IF('Email Raw Data'!R1C1:R900C1=RC[-2],IF('Email Raw Data'!R1C11:R900C11=""Service Desk Queue"",1,0),0))+SUM(IF('Email Raw Data'!R1C1:R900C1=RC[-2],IF('Email Raw Data'!R1C11:R900C11=""(None)"",1,0),0))+SUM(IF('Em ail Raw Data'!R1C1:R900C1=RC[-2],IF('Email Raw Data'!R1C11:R900C11=""Miscellaneous Queue"",1,0),0))" Thanks for you Help |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Helpme with Forumal Array option
RC[-2]
This is not like regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "Adhikarun" wrote: I get the following error while trying to run the Macro, I just started learing. "Unable to set the formulaArray Property of the Range class - Error" Selection.FormulaArray = "=SUM(IF('Email Raw Data'!R1C1:R900C1=RC[-2],IF('Email Raw Data'!R1C11:R900C11=""Service Desk Queue"",1,0),0))+SUM(IF('Email Raw Data'!R1C1:R900C1=RC[-2],IF('Email Raw Data'!R1C11:R900C11=""(None)"",1,0),0))+SUM(IF('Em ail Raw Data'!R1C1:R900C1=RC[-2],IF('Email Raw Data'!R1C11:R900C11=""Miscellaneous Queue"",1,0),0))" Thanks for you Help |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Helpme with Forumal Array option
You have too many characters in your formula.
The maximum length the formula can be to programmatically add it to a range, at least using FormulaArray, is 255. Figure out a way to trim the formula down. Here is a sample macro that demonstrates Sub FormulaArrayMaxTester() ' Construct a formula with 255 characters exactly Dim s As String s = "=SUM(IF(R13C7:R32C7=""a"",R13C8:R32C8))" s = s & "+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+ 1+1+1+1+1+1+1+1" s = s & "+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+ 1+1+1+1+1+1+1+1" s = s & "+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+ 1+1+1+1+1" s = s & "+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1" ' FormulaArray max characters allowed is 255... Debug.Print Len(s) '...so this works Range("A1").FormulaArray = s '.. but if I add one more character, error s = s & "1" Range("A1").Offset(1).FormulaArray = s End Sub -- Regards, Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility "Adhikarun" wrote in message ... I get the following error while trying to run the Macro, I just started learing. "Unable to set the formulaArray Property of the Range class - Error" Selection.FormulaArray = "=SUM(IF('Email Raw Data'!R1C1:R900C1=RC[-2],IF('Email Raw Data'!R1C11:R900C11=""Service Desk Queue"",1,0),0))+SUM(IF('Email Raw Data'!R1C1:R900C1=RC[-2],IF('Email Raw Data'!R1C11:R900C11=""(None)"",1,0),0))+SUM(IF('Em ail Raw Data'!R1C1:R900C1=RC[-2],IF('Email Raw Data'!R1C11:R900C11=""Miscellaneous Queue"",1,0),0))" Thanks for you Help |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Helpme with Forumal Array option
You have too many characters in your formula.
The maximum length the formula can be to programmatically add it to a range, at least using FormulaArray, is 255. Figure out a way to trim the formula down. Here is a sample macro that demonstrates Sub FormulaArrayMaxTester() ' Construct a formula with 255 characters exactly Dim s As String s = "=SUM(IF(R13C7:R32C7=""a"",R13C8:R32C8))" s = s & "+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+ 1+1+1+1+1+1+1+1" s = s & "+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+ 1+1+1+1+1+1+1+1" s = s & "+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+ 1+1+1+1+1" s = s & "+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1" ' FormulaArray max characters allowed is 255... Debug.Print Len(s) '...so this works Range("A1").FormulaArray = s '.. but if I add one more character, error s = s & "1" Range("A1").Offset(1).FormulaArray = s End Sub -- Regards, Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility "Adhikarun" wrote in message ... I get the following error while trying to run the Macro, I just started learing. "Unable to set the formulaArray Property of the Range class - Error" Selection.FormulaArray = "=SUM(IF('Email Raw Data'!R1C1:R900C1=RC[-2],IF('Email Raw Data'!R1C11:R900C11=""Service Desk Queue"",1,0),0))+SUM(IF('Email Raw Data'!R1C1:R900C1=RC[-2],IF('Email Raw Data'!R1C11:R900C11=""(None)"",1,0),0))+SUM(IF('Em ail Raw Data'!R1C1:R900C1=RC[-2],IF('Email Raw Data'!R1C11:R900C11=""Miscellaneous Queue"",1,0),0))" Thanks for you Help |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If Then forumal | Excel Worksheet Functions | |||
| Worksheet forumal help | Setting up and Configuration of Excel | |||
| Seeking Help with a forumal. | Excel Programming | |||
| Having problem with subtotal helpme out | Excel Programming | |||
| forumal does not work | Excel Worksheet Functions | |||
Linear Mode
