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

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

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

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