Excel supports both A1 and R1C1 notation for referencing cells in formulas. Almost everyone uses A1 notation but it can be easier to use R1C1 notation to generate column references in VBA. For instance, to generate a formula that references a column in A1 notation, you need a function that returns A
to Z
for the first 26 columns, then returns two letter column names (such as AA
) for columns past the 26th. With R1C1 notation, you just concatenate C
with a column number (such as C1
or C27
). Lastly, to assign a formula to a cell using R1C1 notation, use the cell's FormulaR1C1 property.
2009-12-03
R1C1 notation in Excel VBA
Subscribe to:
Post Comments (Atom)
I find I prefer A1 in general but every now and then drop into R1C1 for the odd thing. Certainly if you are using .cells(r,c) notation in VBA using R1C1 makes it easy to get the column numbers. But as I pretty much exclusively use named ranges to coordinate VBA and worksheets, its not that big of a win.
ReplyDelete