I am so frustrated, sure hope you can help.
First, I tried this code:
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:
ActiveCell.GoalSeek Goal:="1", ChangingCell:=Range _(ActiveCell.Offset(-9, 0).Address)
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.
Here is a working sample:
Sub GoalSeekMacro() Range("B15").ClearContents Range("B9").Formula = "=sqrt(B15)" Range("B9").GoalSeek _
Goal:=15, _ ChangingCell:=Range("B15")
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).
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 _
They all worked for me. I hope they get you headed in the right direction.