+ Reply to Thread
Results 1 to 2 of 2

Goal Seek Cell Reference Invalid Error

  1. #1
    Registered User
    Join Date
    06-12-2012
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2010
    Posts
    54

    Goal Seek Cell Reference Invalid Error

    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

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,317

    Re: Goal Seek Cell Reference Invalid Error

    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.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1