Formula Array in VBA

[goldie12]

Can someone tell me how you go about using the formula array in VBA? I have the following code:


Range("N10").FormulaArray = _
"=SUM((Miles>=R9C)*(Miles<R9C[1])*(Depth<=RC13)*(Depth>R[1]C13))"


I keep getting an error message stating that "Unable to apply Array Formula" and "Invalid name error"? Any suggestions on how to set the array formula in VBA

Thanks

[Justinlabenne]

FYI, cross-posted here (http://www.mrexcel.com/board2/viewtopic.php?t=156598)

[goldie12]

Sorry again for some reason I can't seem to post the whole line of the code: Let me try this again:\

Range("N10").FormulaArray = _
"=SUM((Miles>=N$9)*(Miles<O$9)*(Depth<=$M10)*(Depth>$M11))"
Selection.AutoFill Destination:=Range("N10:N16"), Type:=xlFillDefault

I get the following error message:

Runtime error '1004'
Unable to set the FormulaArray property of the Range Class

[Bob Phillips]

Worked for me.

I did force it by merging N10 and M10. Do you have merged cells?

[goldie12]

no I don't have any merged cells.

[Bob Phillips]

no I don't have any merged cells.

Have you tried to create the formula in Excel in cel;l N10, and found any problems?

[goldie12]

It works fine now. I had forgot to define my labels. I had to go to the Insert menu then Name then define to input my OFFSETS for the two columns.