VBA loops and Dolver help
 

Hi, I'm very new to VBA but havea solver function that I need to apply to
each row in my spreadsheet. A macro is the best way to do this as there are
100 rows.

I have recorded a macro that does what I want for a single row, but when I
try and create a loop things go wrong. I can't figure out how to refer to the
cells that Solver uses so that they use the right ones for each iteration.
I've tried both the A1 style and the R1C1 style.

I did find some help on the website (but cannot find the page today) that
used a FOR loop with the "Counter" command, but this doesn't seem to work.

In each iteration the columns stay the same, its just that obviously the
rows need to change each time.

Does anyone have any advice? I can post the code I have at present if needed.

Many thanks for any help.

VBA loops and Dolver help
 

yea post some code on here pl

--
gti_jober
-----------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...fo&userid=3063
View this thread: http://www.excelforum.com/showthread.php?threadid=54542

VBA loops and Dolver help
 

Your solver only being used in Col 14?
What rows does your Solver need to look into?


Code:
--------------------

For Counter = 1 To 101

Set curCell = Worksheets("Emission Factors").Cells(Counter, 14)

'SolverReset
SolverOk SetCell:=curCell, MaxMinVal:=2, ValueOf:="0", ByChange:=curCell.Offset(0, 1)
SolverAdd CellRef:=curCell.Offset(0, 4), Relation:=2, FormulaText:= _
"'[Reverse DMRB v2.xls]Input Page'!$C$13"
SolverOk SetCell:=curCell, MaxMinVal:=2, ValueOf:="0", ByChange:=curCell.Offset(0, 1)
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True
SolverOk SetCell:=curCell, MaxMinVal:=2, ValueOf:="0", ByChange:=curCell.Offset(0, 1)
SolverSolve UserFinish:=False

Next

--------------------


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=545426

VBA loops and Dolver help
 

"gti_jobert" wrote:


yea post some code on here pls


--
gti_jobert

OK, here goes! This is code that WORKS. I've got teh loop in but it only
does the one row that the refs refer to. When I try changing the refs to
relative refs, or refering to the "counter" for the row numbers in the refs
(cos teh columns dont' change) but all to no avail.

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 25/05/2006 by JCLenham
'
' Keyboard Shortcut: Ctrl+w
'
For Counter = 102 To 102
SolverOk SetCell:="$N102", MaxMinVal:=2, ValueOf:="0", ByChange:="$O102"
SolverAdd CellRef:="$Q102", Relation:=2, FormulaText:="$C$6"
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001,
AssumeLinear _
:=False, StepThru:=False, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
IntTolerance:=0, Scaling:=False, Convergence:=0.0001,
AssumeNonNeg:=True
SolverSolve
Next Counter

End Sub

VBA loops and Dolver help
 

Why are you using a counter from 102 to 102, it will only use the value
102!! Change the counter to the rows you want to change (For Counter =
102 To 500)


Code:
--------------------

For Counter = 102 To 102

SolverOk SetCell:="$N" & counter, MaxMinVal:=2, ValueOf:="0", ByChange:="$O" & counter
SolverAdd CellRef:="$Q" & counter, Relation:=2, FormulaText:="$C$6"
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001,
AssumeLinear _
:=False, StepThru:=False, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
IntTolerance:=0, Scaling:=False, Convergence:=0.0001,
AssumeNonNeg:=True
SolverSolve
Next Counter

End Sub


--------------------


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=545426

VBA loops and Dolver help
 

I had it set to that to get the refs working rather than introducing lots of
variables at once.

Ah, haven't seen that "& counter" terminology before. Off to try...

"gti_jobert" wrote:


Why are you using a counter from 102 to 102, it will only use the value
102!! Change the counter to the rows you want to change (For Counter =
102 To 500)


Code:
--------------------

For Counter = 102 To 102

SolverOk SetCell:="$N" & counter, MaxMinVal:=2, ValueOf:="0", ByChange:="$O" & counter
SolverAdd CellRef:="$Q" & counter, Relation:=2, FormulaText:="$C$6"
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001,
AssumeLinear _
:=False, StepThru:=False, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
IntTolerance:=0, Scaling:=False, Convergence:=0.0001,
AssumeNonNeg:=True
SolverSolve
Next Counter

End Sub


--------------------


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=545426

VBA loops and Dolver help
 

Many thanks, I've got it working now!!

"gti_jobert" wrote:


Why are you using a counter from 102 to 102, it will only use the value
102!! Change the counter to the rows you want to change (For Counter =
102 To 500)


Code:
--------------------

For Counter = 102 To 102

SolverOk SetCell:="$N" & counter, MaxMinVal:=2, ValueOf:="0", ByChange:="$O" & counter
SolverAdd CellRef:="$Q" & counter, Relation:=2, FormulaText:="$C$6"
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001,
AssumeLinear _
:=False, StepThru:=False, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
IntTolerance:=0, Scaling:=False, Convergence:=0.0001,
AssumeNonNeg:=True
SolverSolve
Next Counter

End Sub


--------------------


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=545426