03 December 2009

R1C1 notation in Excel VBA

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.