Automate Goal Seek in Excel 2007 1

[MattGreer]

I'm trying to automate the Goal Seek function based on the current active cell. What I want to do is have the user click a button and, based on the current active cell, the button will run the goal seek code and solve for the desired number.

The code is simple, but I am stupid when it comes to the RANGE object and using OFFSET.

ActiveCell.GoalSeek Goal:=1, ChangingCell:=ActiveCell.Range(ActiveCell).Offset(-1, 0)

Excel errors out on the word "Range" and says "Argument Not Optional" or some such thing.

How can I reference a cell that is in the same column, but one row above, the current cell and incorporate that into the Goal Seek code?

Thanks!!


Matt

 

[MattGreer]

I always figure these things after the fact, heh.

Sub Close_MB()
Dim rng As Range
'
Set rng = ActiveCell

    ActiveCell.GoalSeek Goal:=1, ChangingCell:=rng.Offset(-1, 0)
    
    rng.Offset(0, 1).Activate
    
Set rng = Nothing


End Sub

I guess one question would be, do I need the "Set rng = Nothing" statement? I had heard at one point that it was good to close such things.

Thanks!!


Matt

 

[PHV]

Why not simply this ?

Sub Close_MB()
ActiveCell.GoalSeek Goal:=1, ChangingCell:=ActiveCell.Offset(-1, 0)
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MattGreer]

Because I am not nearly as awesome as you.

Star for you. Thanks!

Thanks!!


Matt