R1C1 Notation to Declare a Static Column

[wackerpf]

Basically in my VBA I am assigning a formula to a particular cell, and I need to declare an entire column.
So my end goal, for example, is to have this formula in cell A1:
<code>{=AVERAGE(IF(B:B="Yes",C:C))}</code>

So in my VBA I successfully used this line:
<code>[A1].FormulaArray = "=AVERAGE(IF(<b>C[1]</b>="Yes",<b>C[2]</b>))"</code>

The only problem is that I need the reference to remain as B:B and C:C regardless of what cell I put this into, instead of referencing one, and two columns over, respectively.

Normally when I use R1C1 format I would use something like RC4 (leaving the square brackets off) to result in a static column $D, with a dynamic row. So naturally, since C[1] worked referentially, I tried simply using C2 to declare a static $B:$B. This did not work, as Excel simply placed the literal C2 into my formula instead of converting it to column B.

Any suggestions?

 

[iliace]

How about try doing this before that line?

Application.ReferenceStyle = xlR1C1

 

[pgc01]

Hi wackerpf

Try:

Range("A1").FormulaArray = "=AVERAGE(IF(B:B=""Yes"",C:C))"

Remarks:

1 - you were missing quotes around the "Yes"

2 - the .FormulaArray property accepts both the A1 and the R1C1 notations, despite of what the help says

3 - the .FormulaArray property defaults to the A1 notation in case of ambiguity as in the case of the formula you posted. You could force the R1C1 notation with some dummy operation but it would make the formula less efficient.

 

[pgc01]

How about try doing this before that line?

Application.ReferenceStyle = xlR1C1

Hi iliace

Did you try it? It didn't work for me.

I tested with the statements

Application.ReferenceStyle = xlR1C1
Range("A1").FormulaArray = "=AVERAGE(IF(C1=""Yes"",C2))"

and it still defaulted to the A1 notation.

 

[iliace]

No I didn't try it Just thought it might work.

 

[Scott Huish]

The only problem is that I need the reference to remain as B:B and C:C regardless of what cell I put this into, instead of referencing one, and two columns over, respectively.

I assume you are using XL2007, because otherwise a full column array formula is not possible.

Why not this?

Sub test()
Range("A1").FormulaArray = "=AVERAGE(IF($B:$B=""Yes"",$C:$C))"
Range("D7").FormulaArray = "=AVERAGE(IF($B:$B=""Yes"",$C:$C))"
End Sub

With the absolute references, you could also copy and paste it:

Sub test()
Range("A1").FormulaArray = "=AVERAGE(IF($B:$B=""Yes"",$C:$C))"
Range("A1").Copy Range("F7:G15")
End Sub

 

[mikerickson]

If you are using 2007, does

Range("A1").FormulaArray = "=Average(IF(R1C2:R65536C2=""yes"",r1C3:R65536C3))"

fix the issue?

 

[wackerpf]

I wound up just using the $B:$B technique instead. I tend to steer clear of this method so I can use numeric variables to calculate exactly what column or row I need to target and then use the Cells(row,col) or R1C1 technique. I realized, however, that I really had no reason to use a numeric column reference so I just declared it as the lettered column instead (since in this particular case it will always be those columns... no need to calculate).

On a side note, yes I am using 2007. And no, the <code>Application.ReferenceStyle = xlR1C1</code> command did not fix it. Lastly, <code>Range("A1").FormulaArray = "=Average(IF(R1C2:R65536C2=""yes"",r1C3:R65536C3))"</code> would work, sure... but I decided to just go w/ the letters instead... (not to mention XL2007 goes over 1 million rows now)

Thanks for the all the help everyone.