How to reference cells dynamically using VBA

The best way to go through cells dynamically is using the Cells object inside a loop. This way you can refer to a different cell on each pass of the loop.

Use a simple counter variable (you can name it whatever you want), which is increased or decreased on each loop pass. Here are some examples…

Using a ForNext loop

Use For Next when you know the starting row/column as well as the last row/column.

For counter = 2 To 5

Cells(counter, 5).Value = Cells(counter, 3).Value * Cells(counter, 4).Value

Next

Using a DoWhile loop

Use Do While when you either don’t know the starting row/column or the last row/column.

counter = 2

Do While Cells(counter, 1) <> ""

Cells(counter, 5) = Cells(counter, 3).Value * Cells(counter, 4).Value

counter = counter + 1

Loop

Using a DoUntil loop

Use Do Until when you either don’t know the starting row/column or the last row/column.

counter = 2

Do Until Cells(counter, 1) = ""

Cells(counter, 5) = Cells(counter, 3).Value * Cells(counter, 4).Value

counter = counter + 1

Loop