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

Dare to simplify your workflow, get more done with less, and be seen like an Excel Authority?

Download my report 'How to Think Like An Excel Guru'!

Works great for intermediate and advanced Excel users!

We promise to do our best to keep your email safe