Goal Seek Cell Reference Invalid Error

**michaeljoeyeager** · 02-23-2013, 03:20 PM

Friends, I need to iterate goal seek a bunch of times of the same group of cells so I tried to create a macro to do it for me... It worked a couple of times but now I am getting a cell reference error and can't figure out why.

Please Login or Register  to view this content.

I am sure there is a much more eloquent way to program this in but I am a VBA noob. I know this is a popular topic so I have scoured the forums but with no luck.

Also, the goal cell has a nasty formula in it... =(AP3*SIN(AN3))+((AL3*Y3+AQ3*TAN(AK3))*SIN($B$20))+(AQ3*COS($B$20))-AE3
I don't know if that matters. Thanks so much in advance.

-Mike

**MrShorty** · 02-23-2013, 05:20 PM

I am sure there is a much more eloquent way to program this in but I am a VBA noob.

Since it appears you are simply performing the same operation down a column, you should be able to use a For...Next or For Each...Next loop (or a Do While loop or a Do until loop) http://msdn.microsoft.com/en-us/libr...=VS.71%29.aspx.

I observe that after the 3rd time, you have changed the "changing cell" from AQi to ARi.

Beyond that, one would probably have to know what is in column AM, column AQ/AR, and all of the other references in the function.

FWIW, I believe a little algebra will allow you to solve for AQ3 explicitly, allowing you to put a formula in AQ3 that will find AQ3 when AM3=0, and eliminating the need for goal seek.

Goal Seek Cell Reference Invalid Error

LinkBack

Thread Tools

Rate This Thread

Display

Goal Seek Cell Reference Invalid Error

Re: Goal Seek Cell Reference Invalid Error

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions