Refer Cells in Excel Using R1C1 notation
You can use the Cells property to refer cells in Excel using r1c1 notation in a macro. R1C1 stands for row index and column index number. This property returns an Excel Range object that represents a single cell. In the following example, Cells(2,4) returns product of cells(2,2) and cells(2,3) of Excel Sheet1.
Cells(2,4) represents the cell in row 2 and column 4 (D). In standard Excel language we are referring to cell D2. We encountered the cells property usage when we studied the recorded code of a macro and when we looped through the Excel worksheet cells to perform automated calculations in a ‘do while’ loop.
Macro Code for reference:
Sub MyValue()
Worksheets(“sheet1”).Cells(2, 4).Value = Cells(2, 2).Value * Cells(2, 3).Value
End Sub
Implementing the macro:
- Click on Tools in the menu, select Macro and from the drop-down menu select the Visual Basic Editor option
- In the VBA editor click on insert and select module and type the above code to calculate the Amount for the item Pen
- The first two lines of code after the macro name with the apostrophes are just remarks for the user’s benefit
- Of course you can use the shortened code also:
Sub MyValue()
Cells(2, 4) = Cells(2, 2) * Cells(2, 3)
End Sub
Watch the Excel training video below to see the creation and execution of the macro:
Further reading:
How to: Refer to Cells by Using Index Numbers