PDA

View Full Version : Solved: Interpreting Macro Recorder Output



Cyberdude
04-06-2006, 01:13 PM
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
04-06-2006, 01:41 PM
Weird.:huh:

This is what I got when I recorded a macro.

ActiveWorkbook.Names.Add Name:="TestName", RefersToR1C1:= _
"=OFFSET(Main!R2222C46,0,0,COUNTA(Main!C46))"

Cyberdude
04-06-2006, 02:17 PM
Thanx, Norie. I'll investigate to make sure that all columns are the same.

XLGibbs
04-06-2006, 04:54 PM
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
04-08-2006, 04:58 PM
Thanx, XL. I was wondering about that. Gawd, I hate R1C1! :friends:

Bob Phillips
04-09-2006, 01:54 AM
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
04-09-2006, 11:19 AM
Thanks, xl. Now you kknow why I hate R1C1. Whew! :bug: