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

In article ,
says...
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!"