Goal Seek function and Range

[sake14]

Hello,

I am having a hard time at work implement the goal seek function.
I need to allow the code to run through cells that is why i use "i" as a counter.
Every time I try to run it it gives the message "Application defined or Object defined error"
and when I solve this it gives me the error message "range of object worksheet failed"
Any help please....
Thank you very much!!!

I am posting the code....

Sub pds()
Dim i As Long
i = 5
Do Until i = 2323
If Cells(i, 2) <> "" Then
Source = Worksheets(3).Cells(i, 12)
ResultCell = Worksheets(3).Cells(i, 14).Value
Result = Worksheets(3).Cells(i, 2).Value
Range(ResultCell).GoalSeek Goal:=Result, ChangingCell:=Range(Source)
ElseIf Cells(i, 3) <> "" Then
Source = Worksheets(3).Cells(i, 13)
ResultCell = Worksheets(3).Cells(i, 15).Value
Result = Worksheets(3).Cells(i, 3).Value
Range(ResultCell).GoalSeek Goal:=Result, ChangingCell:=Range(Source)
End If
i = i + 1
Loop
End Sub

[Kenneth Hobs]

Welcome to the forum!

When pasting code, please paste between code tags. You can insert the tags by clicking the # and paste between the codes.

Try doing it for one cell before trying a loop.

If you use Option Explicit as the first line of code, you can see that you will need more Dims. From your example, Source is a variant and as used, is just whatever value the cell in the loop has. To set it as a range do it like this:

Sub ken
Dim Source as Range
Set Source = Worksheets(3).Cells(2, 12)
Debug.Print Source.Address, Source.Value
End Sub
The Debug.Print outputs to the Immediate Window. It is a good debugging tool.

So, use the range's Address property if you use Range() in GoalSeek or rather than Range(Source.Address) just use Source directly.


Here is how I used a change event for a sheet to use GoalSeek. Add sheet code in the Sheet object. An easy way to do that is to right click the sheet's tab > View Code.

http://www.vbaexpress.com/forum/showthread.php?38087-Solved-goal-seek-w-value-from-another-workbook

[sake14]

Thank you for the reply and help...but it seems that my excel has some more general problem with the goal seek in vba. I tried it even in its simplest form and subs but I keep getting the same message...When I remove the goal seek from the code everything else works just fine....