FormulaArray

[krets]

I'm trying to set the formula of a cell based on certain criteria. Like this:

If ActiveSheet.Range("K1") = "Cell 200" Then
    mVar1 = "E4:E11"
    mVar2 = "I4:I11"
    mVar3 = "M4:M11"
    mVar4 = "Q4:Q11"
    mVar5 = "U4:U11"
End If

... More If statements here ...

    ActiveSheet.Range("B4").FormulaArray = "=MEDIAN(IF(Week1!" & mVar1 & "<>0,Week1!" & mVar1 & "))"
    ActiveSheet.Range("C4").FormulaArray = "=MEDIAN(IF(Week1!" & mVar2 & "<>0,Week1!" & mVar2 & "))"
    ActiveSheet.Range("D4").FormulaArray = "=MEDIAN(IF(Week1!" & mVar3 & "<>0,Week1!" & mVar3 & "))"
    ActiveSheet.Range("E4").FormulaArray = "=MEDIAN(IF(Week1!" & mVar4 & "<>0,Week1!" & mVar4 & "))"
    ActiveSheet.Range("F4").FormulaArray = "=MEDIAN(IF(Week1!" & mVar5 & "<>0,Week1!" & mVar5 & "))"

That's giving me an error:

"Unable to set the FormulaArray property of the Range class."

So how would I set it then?

 

[PHV]

As far as I know the FormulaArray property should be in the R1C1 notation.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[combo]

In my case all works with any notation, is the worksheet protected?

combo

 

[krets]

Yep, that was it. I wonder why that is?

 

[krets]

combo: Once I changed to R1C1 it started working for me. What version of Excel are you using? 2003 here...

 

[combo]

XP. Recording a macro resulted RC notation. I tried to be as close to the problem as possible, so I put a "E4:E11" regerence style into a string variable add built a string. All worked (the only problem was in case if sheet protection).

combo