Solver

B

Ben Allen

I have a peice of code which adds data to a spreadshet via a form and then
runs a solver command, however, the solver code only runs for row 4 at the
moment. How can i make it so that the row in which data has just been copied
is the one in which the solver runs?
Thanks for any help.

'Add Button
Private Sub Add_Click()
'Data Validation
ActiveWorkbook.Sheets("Bookings").Activate
ActiveSheet.Unprotect
Range("b4").Select
Do
'First Empty Cell
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
'Copy Data
ActiveCell.Value = tourref.Value
ActiveCell.Offset(0, -1) = DateText.Value
ActiveCell.Offset(0, 1) = CountryText.Value
ActiveCell.Offset(0, 2) = PlaceText.Value
ActiveCell.Offset(0, 3) = AdultsText.Value
ActiveCell.Offset(0, 4) = ChildrenText.Value
ActiveCell.Offset(0, 5) = CoachesText.Value
ActiveCell.Offset(0, 6) = MinibusesText.Value
ActiveCell.Offset(0, 7) = TourbusesText.Value
'Solver
SolverOk SetCell:="$K$4", MaxMinVal:=2, ValueOf:="0",
ByChange:="$H$4:$J$4"
SolverAdd CellRef:="$H$4", Relation:=4, FormulaText:="integer"
SolverAdd CellRef:="$I$4", Relation:=4, FormulaText:="integer"
SolverAdd CellRef:="$J$4", Relation:=4, FormulaText:="integer"
SolverAdd CellRef:="$H$4", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$I$4", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$J$4", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$L$4", Relation:=3, FormulaText:="$F$4+$G$4"""
SolverSolve
Range("b4").Select
End Sub

--
Cheers,
Ben

Remove your.tonsils to reply
"You only live once but if you do it right once is enough!"
 
P

Peter Athertn

-----Original Message-----
I have a peice of code which adds data to a spreadshet via a form and then
runs a solver command, however, the solver code only runs for row 4 at the
moment. How can i make it so that the row in which data has just been copied
is the one in which the solver runs?
Thanks for any help.
Click to expand...

Ben

Try this to set the address

Dim nr as Integer
Dim add as range

nr = Application.counta(range("B:B"))+1
add= application.address(nr,11)
SolverOk SetCell:= add

Sorry I freinds just come so not tested


[email protected]
Regards
Peter
 
B

Ben Allen

Peter said:
Ben

Try this to set the address

Dim nr as Integer
Dim add as range

nr = Application.counta(range("B:B"))+1
add= application.address(nr,11)
SolverOk SetCell:= add

Sorry I freinds just come so not tested


[email protected]
Regards
Peter
Click to expand...
Thanks, I get an error though:

'Object doest support this propert or method'
add = Application.Address(nr, 11)

Cheers,
Ben
--
Cheers,
Ben

Remove your.tonsils to reply
"You only live once but if you do it right once is enough!"
 
P

Peter Atherton

Ben

This will get the address as a string and select the cell

Sub test()
Dim nr As Integer
Dim add As String
nr = Application.CountA(Range("B:B")) + 1
add = Cells(nr, 11).Address
Range(add).Select
MsgBox add

End Sub

Regards
Peter
 
T

Tushar Mehta

You are updating columns A:I. But, the Solver model also requires
data/formulas in J:L. You need to provide some more information about
the contents of J:L. The foll. should help you get going...

Option Explicit
Function FindFirstEmptyCell(StartCell As Range) As Range
Dim TempCell As Range
Set TempCell = StartCell
If IsEmpty(TempCell.Value) Then
ElseIf IsEmpty(TempCell.Offset(1, 0).Value) Then
Set TempCell = TempCell.Offset(1, 0)
Else
Set TempCell = TempCell.End(xlDown).Offset(1, 0)
End If
Set FindFirstEmptyCell = TempCell
End Function
Sub CopyValues(StartCell As Range)
With StartCell
.Value = tourref.Value
.Offset(0, -1).Value = DateText.Value
.Offset(0, 1).Value = CountryText.Value
.Offset(0, 2).Value = PlaceText.Value
.Offset(0, 3).Value = AdultsText.Value
.Offset(0, 4).Value = ChildrenText.Value
.Offset(0, 5).Value = CoachesText.Value
.Offset(0, 6).Value = MinibusesText.Value
.Offset(0, 7).Value = TourbusesText.Value
End With
End Sub
Sub createFormulas(StartCell As Range)
'need formulas in J:L and same row as StartCell
End Sub
Sub setSolverParams(StartCell As Range)
SolverReset
With StartCell
'MsgBox Cells(.Row, "K").Address & "," _
& Range(Cells(.Row, "H"), Cells(.Row, "J")).Address
SolverOk SetCell:=Cells(.Row, "K").Address, MaxMinVal:=2, _
ValueOf:="0", _
ByChange:=Range(Cells(.Row, "H"), Cells(.Row, "J")).Address
'Change the rest of the formulas approrpriately
SolverAdd CellRef:="$H$4", Relation:=4, FormulaText:="integer"
SolverAdd CellRef:="$I$4", Relation:=4, FormulaText:="integer"
SolverAdd CellRef:="$J$4", Relation:=4, FormulaText:="integer"
SolverAdd CellRef:="$H$4", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$I$4", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$J$4", Relation:=3, FormulaText:="0"
'I'm not sure the OP contained the correct syntax for the _
next statement...
SolverAdd CellRef:="$L$4", Relation:=3, FormulaText:="$F$4+$G$4"""
End With
End Sub
Sub getSolverResults()
SolverSolve
End Sub
Sub testSolverSetup()
Dim StartCell As Range
Set StartCell = FindFirstEmptyCell(ActiveSheet.Range("b4"))
CopyValues StartCell
createFormulas StartCell
setSolverParams StartCell
getSolverResults
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Top