Working with cells relative to the active cell

Suppose you wanted to take the contents of the current cell, do something with it and then put the result in the cell to the right of the current cell. The target cell's address is neither set in advance nor the active cell. So how do you refer to it? The answer is that you use a Cells property that treats the active cell as 1, 1 and refer to other cells by it's relative co-ordinates.

This is best seen by example. Suppose the current cell was C3, then:

ActiveCell.Cells(1, 1) refers to C3 (i.e. itself)

ActiveCell.Cells(1, 3) refers to E3

ActiveCell.Cells(3, 1) refers to C5

ActiveCell.Cells(0, 0) refers to B2

ActiveCell.Cells(-1, 0) refers to B1

Here's an example routine that takes the current cell and adds 1 to it and puts the result in the cell immediately to the right:

ActiveCell.Cells(1, 2).Value = ActiveCell.Value + 1

Of course, this code assumes that the active cell contains a number and if contains text instead, a run time error will be generated. Later we'll see a general technique for dealing with such errors.

Because the ActiveCell.Cells(row, col) notation returns an equivalent object to ActiveCell, all the same properties and methods can be applied. These are all valid:

ActiveCell.Cells(2, 2).Cut()

ActiveCell.Cells(2, 2).Formula = "=a1 + a2"

ActiveCell.Cells(2, 2).Font.Bold = True

ActiveCell.Cells(2, 3).Paste()