What is wrong with this simple goalseak vba??

Question

I am so frustrated, sure hope you can help.

First, I tried this code:

Sub GoalSeek()

Dim myrange As Range myrange = ActiveCell.Address

Dim myrangechangingcell As Range myrangechangingcell = ActiveCell.Offset(9, 0)

Range(myrange).GoalSeek Goal:=1, ChangingCell:=Range(myrangechangingcell) End Sub

And I got Reference is not valid. Doing some more research, I then tried this code:

Sub GoalSeek()

ActiveCell.GoalSeek Goal:="1", ChangingCell:=Range _(ActiveCell.Offset(-9, 0).Address)

End Sub

This is so weird. I recorded another macro to make sure I had the general syntax right, then I tweaked it. And this exact code I"m posting before, it will work if I use it while the activecell is the same cell where I recorded the macro.

However, it says "reference not valid" if I use it on a new cell, which hasn't been used before.

Answer

Issac,

Here is a working sample:

Sub GoalSeekMacro() Range("B15").ClearContents Range("B9").Formula = "=sqrt(B15)" Range("B9").GoalSeek _

   Goal:=15, _
   ChangingCell:=Range("B15")

End Sub

so for your code, you should have a formula in the activecell (I have the code put one in to increase the chances of success).

Sub GoalSeek()

Dim myrange As Range Dim mRangeChangingCell As Range Set MyRangeChangingCell = ActiveCell.Offset(9, 0) Set myrange = ActiveCell ActiveCell.Formula = "=sin(" & MyRangeChangingCell.Address & ")" myrange.GoalSeek Goal:=1, ChangingCell:=MyRangeChangingCell End Sub

for your third one:

Sub GoalSeek1() Range("F2").Select Range("F2").Formula = "=Sin(" & _

 ActiveCell.Offset(9, 0).Address & ")"

ActiveCell.GoalSeek Goal:="1", ChangingCell:=Range _

  (ActiveCell.Offset(9, 0).Address)

End Sub

They all worked for me. I hope they get you headed in the right direction.