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

• 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 video below to see the creation and execution of the macro: