To select a cell in Excel, you have two basic methods: RANGE and CELLS:
Cells(3, 4).Select 'Selects Row 3, Column 4, i.e. cell D3
Range works well for hard-coded cells. Cells works best with calculated cells, especially when you couple it with a loop:
For i = 1 to 10 Cells(i, 1).value = i ' fill A1 through A10 with the value of i Next i
Note that your focus does not change. Whatever cell you were in when you entered the loop is where you are when you leave the loop. This is way faster than selecting the cell, changing the value, selecting the next cell, etc. If you are watching the sheet, the values simply appear.
There are times when you are processing a list when you might want to look at the values in the same row, but a couple of columns over. You can accomplish this best with the .Offset clause. You might see the .Offset clause when you record a macro using relative references:
This is both confusing and overkill. Translated into English, it takes the current cell (ActiveCell) and selects the row that is one row down from the current row and in the same column. The “Range(“A1″)” clause is not necessary. So if you want to stay in the current cell and read a value two columns to the right, you could use syntax like the following:
strMyValue = ActiveCell.Offset(0,2).Value
If you are in cell D254, the code above will reference the cell F254 and read its value into the variable strMyValue. This is far more efficient than selecting the cell two columns to the right, processing your data, then remembering to select two columns to the left and continue.
If you want to offset to a column to the left of you or a row above you, use a negative number. If you are in cell G254, the code ActiveCell.Offset(-3,-2).Select will select E251 (3 rows up and 2 columns left).
You can loop through a list much more efficiently with Offset. It is easier to program and way faster to execute.
While ActiveCell.Value <> "" strFirstName = ActiveCell.Value strLastName = ActiveCell.Offset(0,1).Value dblSalary = ActiveCell.Offset(0,2).Value ActiveCell.Offset(0,2).Value = dblSalary * 1.05 'give a 5% raise MsgBox(strFirstName & " " & strLastName & ": Your new salary is " & dblSalary) ActiveCell.Offset(1,0).Select Wend
You would probably want to format the salary for currency, but this is the general idea.