learn microsoft excel

How to access worksheet cells using cell property in an Excel macro

Excel 2007
Excel 2003
Home
Macros Excel 2003
Excel 2010
About Us
Free Ebook
Offline Training
Blog
Excel VBA Books

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 asl with numbers 1, 2, etc.
Now let's see the training video on how this is implemented practically.



Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010
About Us Free Ebook Offline Training Blog Excel VBA Books