Accessing Excel worksheet cells
using cells property in macros

Accessing Excel worksheet cells using cells property in macros: No matter what you do in Excel, you need to access a single cell or a group of cells to place your data. Once the data is placed in a cell, you will need to perform formatting, calculations and/or analysis. This will again be done through accessing the cell or cells. There are two simple ways to access a single cell or a group of cells: using the range property combined with the alpha-numeric value of the coulumn and row or using the 'cells' properties . For example, you will write 'Range("A1") to access the Excel worksheet cell in column A and row 1 or Cells(1,1) which represents cell in row 1 and column 1, also known as the 'RC' accessing convention, where R stands for row and C stands for column. Normally, when you start Excel the columns are numbered using alphabets a, b,c etc. and the rows are numbered 1,2,3,etc. You can change the display of the rows and columns in Excel 2007 by clicking on the 'Office button' icon, selecting 'Excel Options' and selecting R1C1 reference style in the 'Formulas' options.
Let's say, you now wanted to display 'Name' in bold in the Excel worksheet cell 'B4'. You would write the following line of code in your macro:
Range("B4")="Name"
Range("B4").Font.Bold=True
You could also access 'B4' by writing the following macro code:
Cells(4,2)="Name"
Cells(4,2).Font.Bold=True
Accessing cells using the 'cells' properties has advantages: For example, if you wanted to enter values in cells 'B2' to 'B8' using a 'for loop' statement, you would do it as follows:
for counter=2 to 8
Cells(counter,2)=counter*10
Next counter
The cells 'B2' to 'B8' would be filled with 20,30,40,50,60,70,80.
Similarly if you had 'Name','Salary' and 'Perks' headings in 'A1','B1' and 'C1' and data like "Tom', 'Dick' and 'Harry' in cells A2,A3 and A4 and '5000','6000' and '7000' in cells B2, B3 and B4 and you wanted to calculate the perks at 50% of salary, you could easily use the following code to automate the calculation of 'perks'.
x=2 'this defines the starting row
Do while Cells(2,3)<> ""
Cells(x,3)=Cells(x,2)*0.5
x=x+1
loop
The relevant 'perks' cells c2,c3 and c4 would display 2500, 3000 and 3500 respectively after running the macro.

The following short video explains the concept.

Accessing Excel worksheet cells using cells property in macros