R1C1 riddle

Can anyone explain why the first line of code is 'skipping rows' and
the second and third are not? What am I missing?

Range("a1.b10").Formula = Array("=rc[1]", "=""Row: ""&ROW()")

Range("a1.b10").FormulaArray = Array("=rc[1]", "=""Row: ""&ROW()")
Range("c1.c10").Formula = "=""Row: ""&row(rc[1])"

Checked in Excel 2000 and Excel XP.

Regards,
Andre

Yes, this is a quirk I came across a while back, and had to use a different
method to get around it. It appears that filling a range with a formula
automatically updates the cell reference much like entering a formula in a
cell then dragging it downward. I've never used the "=rc[1]" syntax but it
appears that the [1] means offset by one, and the automatic offset update of
filling the range is why the offset becomes 1, 3, 5.

I tried these statements stepping through the debugger to see how the
formulas are entered:

Private Sub Workbook_Open()

Range("A1:B2").ClearContents
Range("A1:A2").Formula = "=B1"

Range("A1:B2").ClearContents
Range("A1:A2").Formula = Array("=B1", "=D1")

Range("A1:B2").ClearContents
Range("A1:A2").FormulaArray = "=B1"

Range("A1:B2").ClearContents
Range("A1:A2").FormulaArray = Array("=B1", "=D1")

Range("A1:B2").ClearContents
Range("A1:B1").Formula = "=B1"

Range("A1:B2").ClearContents
Range("A1:B1").Formula = Array("=B1", "=D1")

Range("A1:B2").ClearContents
Range("A1:B1").FormulaArray = "=B1"

Range("A1:B2").ClearContents
Range("A1:B1").FormulaArray = Array("=B1", "=D1")

End Sub

It seems like the Array("", "") statement fills across the row, i.e. the
range("A1:A2") receives only the first element of the Array(), which is
subsequently copied (and offset) downward. Try it and see if you follow what
it does.


"Scriptick" wrote:

> Can anyone explain why the first line of code is 'skipping rows' and
> the second and third are not? What am I missing?
>
> Range("a1.b10").Formula = Array("=rc[1]", "=""Row: ""&ROW()")
>
> Range("a1.b10").FormulaArray = Array("=rc[1]", "=""Row: ""&ROW()")
> Range("c1.c10").Formula = "=""Row: ""&row(rc[1])"
>
> Checked in Excel 2000 and Excel XP.
>
> Regards,
> Andre
>
>