I'm grasping at straws here, so any help would be great (ie. I have no idea what I'm doing with VBA).
I'm trying to solve a circular reference problem in Excel by creating a goal seek macro - basically
S3 = M + S2, S3 = L * G. I want to goal seek S3 to equal L * G by changing M.
So, I've put
L * G in cell H32,S3 in H18,M in G18,S2 in F18and this is what I've gathered so far.
Sub GoalSeek()
Dim x As Integer
x = Range("H32").Value
Range("H18").GoalSeek Goal:=x, ChangingCell:=Range("G18")
End Sub
I'm getting a "Reference is not valid" error, ideas? Thanks!
GoalSeek will throw an "Invalid Reference" error if the GoalSeek cell contains a value rather than a formula or if the ChangingCell contains a formula instead of a value or nothing.
The GoalSeek cell must contain a formula that refers directly or indirectly to the ChangingCell; if the formula doesn't refer to the ChangingCell in some way, GoalSeek either may not converge to an answer or may produce a nonsensical answer.
I tested your code with a different GoalSeek formula than yours (I wasn't quite clear whether some of the terms referred to cells or values).
For the test, I set:
the GoalSeek cell H18 = (G18^3)+(3*G18^2)+6
the Goal cell H32 = 11
the ChangingCell G18 = 0
The code was:
Sub GSeek()
With Worksheets("Sheet1")
.Range("H18").GoalSeek _
Goal:=.Range("H32").Value, _
ChangingCell:=.Range("G18")
End With
End Sub
And the code produced the (correct) answer of 1.1038, the value of G18 at which the formula in H18 produces the value of 11, the goal I was seeking.
I think your issue is that Range("H18") doesn't contain a formula. Also, you could make your code more efficient by eliminating x. Instead, change your code to
Range("H18").GoalSeek Goal:=Range("H32").Value, ChangingCell:=Range("G18")
Source: Stackoverflow.com