Excel visual basic/goal seek

QuestionEdit

To put it simply I have two main queries.

I want to use a kind of "goal seek" function in the Visual Basic Editor inso that I can set the target cell value to a value specified in another cell by changing a third cell. I know this is easily done manually but I would like to set a button on screen which will run a macro.

Secondly I have a list of data arranged by date.I am looking to set up a form (which I know how to do) so that the user can select a range of data to exclude from the calculations (i.e. a start date and an end date).

Any help you can give on any of these queries will be much appreciated.

Dean

AnswerEdit

Dean

Sub TypeofGoalSeek() Dim CellwithFormula As Range Dim CelltoChange As Range, rGoal As Range Set rGoal = Range("J5") ' The other cell with the value Set CellwithFormula = Range("J4") ' The cell that should equal rGoal when done (Target cell) Set CelltoChange = Range("J3") ' the cell to change CellwithFormula.GoalSeek Goal:=rGoal.Value, ChangingCell:=CelltoChange MsgBox "Solution Value is: " & CelltoChange.Text End Sub

the Target cell must contain a formula that uses the value in the CelltoChange.

As to question two, something like the below may be what you want.

Dim StartDate as Date, EndDate as Date Dim rStart as Range, rEnd as Range Dim rng as Range, r as Range

set StartDate = cdate(Userform1.Textbox1.Text) set EndDate = cdate(Userform1.Textbox2.Text) set r = Worksheets("Data").Range("A1:A500") res1 = Application.Match(clng(StartDate),r,0) res2 = Application.Match(clng(EndDate),r,0) if not iserror(res1) and Not iserror(res2) then

 set rStart = r(res1)
 set rEnd = r(res2)
 set rng = Range(rStart,rEnd)
 msgbox "Range to exclude is " & rng.Address(0,0)

else

 msgbox "Dates not found in Data"

end if