solver setcell= to an argument name rather than a cell in the worksheet

[Tbeek]

I would like to use solver in VBA and setcell= to an argument name
rather than a cell in the worksheet so I can keep the overhead in VBA.

I am writing a least squares routine that will make a function for
polynomial fits, similar to how trend or forcast work

Here is my code, it does not work in VBA because I am mixing ranges
with variables and such.

' Function created by Travis Beek
Function Poly2(Known_Xs, Known_Ys, New_X, Optional Statistics)

If Known_Xs.Rows.Count <> Known_Ys.Rows.Count Then
MsgBox "Known_Xs does not have the same number of rows as Known_Ys."
& Chr(13) _
& "The answer in " & ActiveCell.Address & _
" will not be correct until they match.", vbExclamation, "Warning"
End If

For n = 1 To Known_Xs.Rows.Count
X = Known_Xs.Item(n, 1) 'ref(r,c) is relative to the range, not the
sheet
y = Known_Ys.Item(n, 1)
a = 1 'should the constants be outside the loop?
b = 1
c = 1
Sumsqs = (y - (a * X ^ 2 + b * X + c)) ^ 2 + Sumsqs
Next n

SolverOK SetCell:=Sumsqs, MaxMinVal:=2, ByChange:="a,b,c"
SolverSolve
Poly2 = Sumsqs


' For Each blah In Known_Xs.Cells 'also works, loop repeats at
the "Next" operator
' total = total + blah.Value
' Next

End Function

 

[Dana DeLouis]

I'm not exactly sure of the math, but here is a look at part of your code...

a = 1 'should the constants be outside the loop?
b = 1
c = 1
Sumsqs = (y - (a * X ^ 2 + b * X + c)) ^ 2 + Sumsqs
Next n

SolverOK SetCell:=Sumsqs, MaxMinVal:=2, ByChange:="a,b,c"

The "SetCell" and "ByChange" are references to Cells on the worksheet.
Solver can not work on vba variables. The reference to "SetCell" would be a
function on your worksheet. The "ByChange" reference would be to three
cells on your worksheet that can be changed by Solver. You can give the
three cells a Name like "a", "b", and "c_" to make your code a little easier
to read.

HTH.

 

[Tbeek]

I would like to use VBA variables not cells on the worksheet, but if
can't, so be it. If anyone knows how to please post.

So lets say this is my code then:
Function Poly2(Known_Xs, Known_Ys, New_X, Cell_for_SumSqs As Range, a
b, c, Optional Statistics)

'Cell_for_Sumsqs, a, b, and c are each a cell on the worksheet
'I need to paste an elaborate formula that depends on the number o
data pairs, into the range Cell_for_Sumsqs, but the following does no
work

Range(Cell_for_SumSqs).Activate
ActiveCell.FormulaR1C1 = "=25" ' 25 is just to see if it works

'the least squares formula is as follows:
' Sumation from i=1 to n of (yi-(a*xi^2+b*xi+c))^2
' how would you write that into a cell when the number of xi,yi pair
depends on the langth of the argument range Known_Xs (or Known_Ys)

Thanks for any help
Travi