Solved: Interpreting Macro Recorder Output

[Cyberdude]

I ran the macro recorder to see how it would create a macro to define a name for the following formula:

=OFFSET(Main!$AT$2222,0,0,COUNTA(Main!$AT:$AT))

It created the following statement which I don?t understand:

ActiveWorkbook.Names.Add Name:="TestName", RefersToR1C1:= _
?=OFFSET(Main! R2222C[26],0,0,COUNTA(Main!C46))"

where column letter ?AT? is column number 46.

1. Notice on the right end of the OFFSET formula that the R1C1 format is C46 whereas the OFFSET formula that I entered has $AT:$AT. Why the difference??

2. Notice on the left side of the formula I wrote I have $AT$2222 which it translated for R1C1 reasons to be R2222C[26]. Why is column ?AT? written as ?C[26]? here, but written as ?C46? on the right end?? Why 26 instead of 46?? What is the significance of the brackets here??

[Norie]

Weird.

This is what I got when I recorded a macro.
[vba]
ActiveWorkbook.Names.Add Name:="TestName", RefersToR1C1:= _
"=OFFSET(Main!R2222C46,0,0,COUNTA(Main!C46))"[/vba]

[Cyberdude]

Thanx, Norie. I'll investigate to make sure that all columns are the same.

[XLGibbs]

IN R1C1 the brackets represent RELATIVE, the lack of brackets represent absolute

so C[26] is the same as $C26
and C46 is $C$46

With offset formula cell references such as that named range, you would always want to enter them with A1 format and absolute references $A$1, otherwise the reference won't work properly, and will change inappropriately.

[Cyberdude]

Thanx, XL. I was wondering about that. Gawd, I hate R1C1!

[Bob Phillips]

so C[26] is the same as $C26
and C46 is $C$46

No, R2222C[26] is the same as x$2222 where x is the column 26 columns to the right of the activecell when the macro was recorded. If you are in column T at the time, that will refer to AT$2222, column C refers to C$2222 and so on.

[Cyberdude]

Thanks, xl. Now you kknow why I hate R1C1. Whew!