Syntax to loop through a range of cells and change a cell value

Question

Currently, cell B2 contains a number that gets referenced when using Solver to generate a certain output. Different values in B2 would generate different values, and I am trying to create code that would determine which value would generate the highest value.

The code should use a loop to go through a row of cells (let's say a row B10:B20) each containing a different number, and for each cell, plug the value into B2, and then run Solver to generate an output.

For example, it should replace B2 with B10, run solver, then replace B2 with B11, run solver again, etc. How do I make it keep changing the value of B2 to the following cell?

Tim Williams Tim Williams · Accepted Answer · 2013-11-27T05:59:33

dim c as range
for each c in activesheet.range("B10:B20").cells
    activesheet.range("B2").value = c.value
    'etc
next c

Syntax to loop through a range of cells and change a cell value

1 Answers