March 1, 2014

Access Excel Worksheet Cells Using Cells Property in VBA

How to access Excel worksheet cells using cells property in VBA  or Excel macro
Another important and useful way to access worksheet cells is to use the cell property in an Excel macro. Because the cell property defines the row-index and the column-index numbers of each cell, you can use the feature to loop through a range of cells and perform calculations or analysis. For example cells(1,1) would access the range ‘A1’ and cells(1,2) would allow you to enter data in cell ‘B1’.

  • Click on the developer tab
  • Select Visual Basic from the Code group
  • In the new Microsoft Visual basic window that pops-up select insert fom the menu-bar
  • Click on module
  • You get a workspace to write the code which is given below

Sub access_cells_using_cell_property()
Cells(3, 1) = “Name”
Cells(3, 2) = “Salary”
End Sub

We have also shown how to view the Excel worksheet in the ‘R1C1’ style where the columns and the rows are both numbered starting from 1, 2, etc. This will also give you an idea on how the application works in this mode. Now A3 is R3C1 as shown in the ‘name box’. Just observe carefully!

  • Click on the Microsoft Office Button
  • Select Excel Options at the bottom right
  • In the Excel Options Window that pops up select formulas and under ‘working with formulas’ check the box next to ‘R1C1 reference style’
  • Now your worksheet shows the columns with numbers 1, 2, etc.

Now let’s see the training video on how this is implemented practically.

2 thoughts on “Access Excel Worksheet Cells Using Cells Property in VBA

  1. Dear Sir,
    I am watching your videos it,s very easy to learn.
    Dear sir i am love coding kindly help me how learn easily and suggest book of VBA
    Ashok Kumar

Comments are closed.