Keep getting runtime error 1004

[YehWei]

Hi,
I'm trying to implement the following, but just couldn't figure out why
I keep getting a runtime error 1004 on Unable to set FormulaArray
Property of the Range Class. Can anyone enlighten me please? Thanks!

Dim NumRows As Integer
Dim NumBins As Integer
Dim ICol As Integer
Dim IRow As Integer
Dim sRngCol As String
Dim sRngR As String
Dim sRngC As String
Dim sRngLot As String

For IRow = 2 To NumRows
For ICol = 1 To NumBins
sRngCol = "rngCol" & ICol + 4
sRngR = "R" & IRow
sRngC = "C" & ICol
sRngLot = sRngR & sRngC
ActiveSheet.Cells(IRow, ICol).FormulaArray = _
"=AVERAGE(IF(rngCol1='" & sRngLot & ",'" & sRngCol & "))"
Next ICol
Next IRow

 

[JE McGimpsey]

Without knowing what you're trying to accomplish, it seems to me that
removing the two single quotes in the formula string will allow it to be
parsed.

 

[YehWei]

Well, I'm actually trying to give an R1C1 value to sRngLot and sRngCol
that can be used in the FormulaArray. Removing the single quotes does
not help.

 

[JE McGimpsey]

Guess I don't understand what you're trying to do, then. Using the
single quotes creates a syntax error in the formula.

How would that give an R1C1 value to sRngLot?

Perhaps if you indicated what the AVERAGE formula should look like...?

 

[Dave Peterson]

And I bet you read VBA's help that said arrayformulas had to be passed in R1C1
reference style.

If your formula is easier to do in A1 reference style, try that in your code.

ps. Sometimes if you build the formula in the worksheet, then post that to the
newsgroup, it'll be easier for others to figure out what you're looking for.

 

[YehWei]

Well, I guess I should have simplified it earlier... What I would like
to achieve is very simple, select consecutive cells and assign a
formulaArray. The variables are RC[] in the formual, which always
reference to a cell in the first column of the first row, and rngCol[],
which is a named range whose name increases...

Range("B2").Select
Selection.FormulaArray = "=AVERAGE(IF(rngCol1=RC[-1],rngCol2))"

Range("C2").Select
Selection.FormulaArray = "=AVERAGE(IF(rngCol1=RC[-2],rngCol3))" and so
on...

Praying for enlightening... going crazy just over this FormulaArray...
Thanks in advance...

 

[YehWei]

I think I've managed to isolate the problem with the error. It's the
incremental variable in the Average formula that cannot be recognized.
for example, I can't use rngCol to change the rngCol2 to rngCol3 for
each loop for formula array, gives me an error. Any pointers please?

 

[Dave Peterson]

I'm still having trouble with the range names, but maybe this will be closer:

Option Explicit
Sub testme()

Dim NumRows As Integer
Dim NumBins As Integer
Dim ICol As Integer
Dim IRow As Integer
Dim sRngCol As String
Dim sRngR As String
Dim sRngC As String
Dim sRngLot As String

NumRows = 8
NumBins = 3

For IRow = 2 To NumRows
For ICol = 2 To NumBins '<--did you want to start with column 1???
sRngCol = "rngCol" & ICol + 4
sRngR = "R" & IRow
sRngC = "C" & ICol
sRngLot = sRngR & sRngC
ActiveSheet.Cells(IRow, ICol).FormulaArray = _
"=AVERAGE(IF(rngCol1=rc1," & sRngCol & "))"
Next ICol
Next IRow

End Sub

In R1C1 reference style, RC1 means the same row, column 1. Maybe that's enough
to make it work correctly???

I'm not quite sure why you would start numbins at 1, though. That gave me a
circular reference error when I plopped that formula into a cell in column A.

Well, I guess I should have simplified it earlier... What I would like
to achieve is very simple, select consecutive cells and assign a
formulaArray. The variables are RC[] in the formual, which always
reference to a cell in the first column of the first row, and rngCol[],
which is a named range whose name increases...

Range("B2").Select
Selection.FormulaArray = "=AVERAGE(IF(rngCol1=RC[-1],rngCol2))"

Range("C2").Select
Selection.FormulaArray = "=AVERAGE(IF(rngCol1=RC[-2],rngCol3))" and so
on...

Praying for enlightening... going crazy just over this FormulaArray...
Thanks in advance...

 

[YehWei]

Well, I had to play around with the numbers, but it works now. Thanks
to everybody who has helped me. Thank you very much!

For ICol = 1 To NumBins
RngCol = "rngCol" & (ICol + 4)
ActiveSheet.Cells(2, ICol + 3).FormulaArray = _
"=AVERAGE(IF(rngCol1=R[0]C[" & -ICol - 2 & "]," & RngCol &
"))"
Next ICol