Create input box in VBA for Goal Seek

[eh9879a]

I'm having trouble with my code for goal seek. It should be asking for input for the goal cell, but taking ranges for the other inputs. My cell G5/y has a formula. Using GoalSeek outside of VBA works perfectly. But using this code, I get a 1004 error "Reference Not Valid". The error highlights this line in yellow: "y.goalseek goal:=Z, ChangingCell:=x". VBA also changes the capitalization from y.GoalSeek Goal:=Z to all lowercase. Don't know why.

I'm new to VBA and mostly trying to learn from forums like this and trial and error. Any basic tips would be really appreciated in addition to figuring out why this won't run.

Sub GoalSeekVBA()

Dim x As Range
Dim y As Range
Dim Z As Range


Set x = Range("I6")
Set y = Range("G5")
Z = Application.InputBox("Please Enter Target Income", "Goal Seek Value")


y.goalseek goal:=Z, ChangingCell:=x


End Sub

 

[problemss]

Z should not be a Range, but a numeric. Set it to Long.

Dim z As Long

 

[eh9879a]

Still have the same error...I simplified the code to this though:

Sub GoalSeekVBA()

Dim z As Integer


z = Application.InputBox("Please Enter Target Income", "Goal Seek Value")


Range("G5").goalseek goal:=z, ChangingCell:=Range("I6")


End Sub

 

[problemss]

Use long. Depending on the value you are inputting, you are probably exceeding the capacity for integer.

what value are you typing in?