Refer Cells Using Index Numbers VBA

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

Leave a Reply

Your email address will not be published. Required fields are marked *