Day 3 — Cells, Range and Offset

Two ways to point at a cell, and knowing which to use when is what makes a macro readable.

Cells and Range point at the same thing

Cells(row, column) → Cells(2, 3) is C2
Range("address") → Range("C2") is also C2

Sub TwoWays()
    Debug.Print Cells(2, 3).Value
    Debug.Print Range("C2").Value
    Debug.Print Cells(2, 3).Address
End Sub

Which one to use Range when the address is fixed and you want it readable: Range("A1").Value = "Report".
Cells when a number is doing the work - and that means every loop, because Cells(i, 8) can have a changing i in it and Range("H2") cannot.

Reading and writing

.Value is the contents of the cell. Read from it, assign to it.

Sub WriteSomeCells()
    Range("H1").Value = "Total"
    Cells(2, 8).Value = 156000
    Cells(3, 8).Value = Cells(3, 6).Value * Cells(3, 7).Value

    Debug.Print "H3 now holds", Cells(3, 8).Value
End Sub

Three cells outlined in gold on the sheet below - H1, H2 and H3. Everything else is untouched.

Offset - move relative to where you are

.Offset(rows, columns) steps away from a cell. Positive numbers go down and right, negative go up and left.

Sub Offsets()
    Debug.Print Range("C2").Value
    Debug.Print Range("C2").Offset(0, 3).Value
    Debug.Print Range("C2").Offset(1, 0).Value
    Debug.Print Range("C5").Offset(-1, 0).Value
End Sub

C2 is North. Three columns right is F2, the units. One row down is C3. Offset is how you say "the cell next to this one" without working out its address.

A block of cells

A Range does not have to be one cell. Range("A1:C5") is a rectangle, and it knows its own shape.

Sub BlockFacts()
    Debug.Print "Address:", Range("A2:D10").Address
    Debug.Print "Rows:", Range("A2:D10").Rows.Count
    Debug.Print "Columns:", Range("A2:D10").Columns.Count
    Debug.Print "Cells in total:", Range("A2:D10").Count
End Sub

.Value on a block is not one value Debug.Print Range("A2:D10").Value fails, and the error confuses everybody the first time. Reading a whole block gives you a two-dimensional array, not a value - that is Day 10, and it turns out to be the fastest trick in VBA.
Writing is simpler: Range("H2:H17").Value = 0 puts 0 in all sixteen cells at once. Try it in the box above.

Which sheet is it writing to?

Cells(2, 8) with nothing in front of it means "the active sheet" - whichever one happens to be on screen. That is fine while learning and dangerous in a real report, because the wrong sheet being active silently ruins your data.

Sub BeSpecific()
    Debug.Print Worksheets("Sales").Name
    Debug.Print Worksheets("Sales").Cells(2, 3).Value
    Debug.Print Worksheets("Sales").Range("D2").Value
End Sub

Name the sheet once you are past practising. It costs eleven characters and removes a whole category of bug.

Try these yourself

Print the salesperson and product from row 9 using Cells, then again using Range.
Use Offset from Range("A2") to print the unit price on the same row.
Write "Order Value" into H1 and the row 4 total into H4.
How many cells are in Range("B2:F17")? Check your answer with .Count.
Explain when you must use Cells rather than Range, and why.

← PreviousDay 2 — Variables, types and Option Explicit Next →Day 4 — If, ElseIf and Select Case