Solved: charting help needed

[austenr]

I have two modules. One I cleaned up from where someone used a macro recorder to record the code. I guess the first problem is, does anyone see any differences in the two codes funtionally?

Sub prova1()
'
' prova1 Macro
' Macro recorded 12/10/2004 by GV
'
' Keyboard Shortcut: Ctrl+g
'
Range("B7").Select
ActiveCell.FormulaR1C1 = "0"
Range("B7").Select
Selection.Copy
Range("B8:B47").Select
ActiveSheet.Paste
SolverOk SetCell:="$C$7", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$7"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$8", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$8"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$9", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$9"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$10", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$10"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$11", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$11"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$12", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$12"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$13", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$13"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$14", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$14"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$15", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$15"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$16", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$16"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$17", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$17"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$18", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$18"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$19", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$19"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$20", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$20"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$21", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$21"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$22", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$22"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$23", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$23"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$24", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$24"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$25", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$25"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$26", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$26"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$27", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$27"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$28", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$28"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$29", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$29"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$30", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$30"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$31", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$31"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$32", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$32"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$33", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$33"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$34", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$34"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$35", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$35"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$36", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$36"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$37", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$37"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$38", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$38"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$39", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$39"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$40", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$40"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$41", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$41"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$42", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$42"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$43", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$43"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$44", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$44"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$45", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$45"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$46", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$46"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$47", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$47"
SolverSolve UserFinish:=True
ActiveWindow.SmallScroll Down:=10
Range("F5").Select
End Sub


Cleaned up version:


Sub prova1()

'this is the same as the Sheet Macro
'was assigned to Ctrl + G

Dim i As Long
Dim eMsg As Long
Dim sb As Boolean

'errors?
On Error GoTo endo

'speed
With Application
.ScreenUpdating = False
'get statusbar status
sb = .StatusBar
'don't do calc event
.EnableEvents = False
End With
'do on data sheet
Sheets("Data").Activate

'clear previous values
'Range("B7:B47") = 0
'solver for row 7 to 47
For i = 7 To 47
SolverOk SetCell:=Range("C" & i), MaxMinVal:=3, ValueOf:="0", ByChange:=Range("B" & i)
SolverSolve UserFinish:=True
Application.StatusBar = " Solving row " & i & " of 47"
Next i

'cleanup
Sheets("Chart").Activate


'reset
With Application
.Calculate
.ScreenUpdating = True
.StatusBar = sb
.EnableEvents = True
End With

Exit Sub

'come here if errors
endo:

'cleanup
Sheets("Chart").Activate
Range("B7:B47").Clear

'reset
With Application
.Calculate
.ScreenUpdating = True
.StatusBar = sb
.EnableEvents = True
End With

eMsg = MsgBox("Error " & Err.Number & " " & Err.Description, vbCritical)

End Sub


Now for the problem. The user can enter variables into a worksheet and SOLVER (Boy I'm beginning to hate that word) :devil2: calculates and updates a chart. Im going to attach the workbook. Anyway, if you go to the chart tab and enter the following in the tan boxes from top to bottom (2000, 12, 50) in the chart worksheet and press the update button, you get this screwey looking line on the chart which forces the zero line up and adds negative axes lines under zero. This happens when you use the attached workbook using the cleaned up code. There is an old workbook (can post that if you like) that uses the macro recorded code; if you enter the same parameters in that WB you don't get that weird line and the chart looks correct.

This does not happen all the time. Just with some sets of parameters. Any help is appreciated.

[daniel_d_n_r]

just at a glance I can see the first code handles each cell individualy,
while the second handles a range/array.
I have never used solver ,heh, and don't really understand the code, but it sounds to me as if somthing is accumulating in the second code causing the Lbound to fall below the x axis (negatives).

Im off to work in a minute so I havn't time to look it over,however if you step through the code using F8 and watch the values in the Locals window I think you will see the exact point at which the codes go their seperate ways.

:dunno

[Andy Pope]

Is the Rate suppose to go back up again at cell B15?