Formula Array

Oaktree

MrExcel MVP

Joined

Jun 20, 2002

Messages

8,120

Office Version

1. 365

• Jul 12, 2005

• #2

You're missing an operator in your second criterion.
Perhaps you want Miles=R[1]C13 instead of MilesR[1]C13 ?

 

Upvote 0

G

goldie12

Board Regular

Joined

Apr 19, 2005

Messages

105

• Jul 12, 2005

• #3

Sorry I listed the wrong code. It should be:

Range("N10").FormulaArray = _
"=SUM((Miles>=N$9)*(Miles<O$9)*(Depth<=$M10)*(Depth>$M11))"

Selection.AutoFill Destination:=Range("N10:N16"), Type:=xlFillDefault

This is the code that I am having problems with. Any suggestions. Thanks

 

Upvote 0

Oaktree

MrExcel MVP

Joined

Jun 20, 2002

Messages

8,120

Office Version

1. 365

• Jul 12, 2005

• #4

Same comment though... Assuming miles is a named range, your second criterion still needs an operator.

Miles$M11 should probably be Miles=$M11

 

Upvote 0

G

goldie12

Board Regular

Joined

Apr 19, 2005

Messages

105

• Jul 12, 2005

• #5

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

Code:

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

 

Upvote 0

Oaktree

MrExcel MVP

Joined

Jun 20, 2002

Messages

8,120

Office Version

1. 365

• Jul 12, 2005

• #6

You may want to use a range instead of "selection" in your fill, but this works fine for me:

Code:

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

 

Upvote 0

G

goldie12

Board Regular

Joined

Apr 19, 2005

Messages

105

• Jul 12, 2005

• #7

Thank you oaktree.

 

Upvote 0