Solver in VBA Help

Newton

I'm trying to write a macro that uses the Solver, but I'm having a hard time getting it to run right. I have a large table of values, with one column that I'm trying to solve for by setting values in a second column. I want the solver to run through and find a solution for each row, so I've been trying to set it up as a loop to run the solver for a fixed number of rows. What I have is this:

for i=4 to 164

SolverAdd CellRef:="$C$i", Relation:=1, FormulaText:="90"
SolverOk SetCell:="$D$i", MaxMinVal:=3, ValueOf:="170", ByChange:="$C$i"
SolverSolve

next i


If I put in a value for i and just run it for a single row, it works fine, but it doesn't work at all when I try to do it with the steps involved. Is there a way to make the solver iterate properly? I really don't want to have to run the solver manually 160 times.

chknsandwich

I have no exp with the solver but some exp with programming in general.

Try using "$C$" & i instead of "$C$i".

The reason being that "$C$i" is taken as a literal value. If you replace i in the source with a word ie "$C$bob" it literally means "bob" not the value of the variable named "bob".

Newton

Thanks, I'll try that. This is all on my work computer so it will have to wait until tomorrow, though.