dcsimg
Results 1 to 4 of 4

Thread: Goal Seek Macro Giving 400 Error or Compile Error: Invalid or Unqualified Reference

  1. #1

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Goal Seek Macro Giving 400 Error or Compile Error: Invalid or Unqualified Reference

    Hi,

    I'm using the below vba code to run a Goal Seek macro in my excel file. AC3 is a formula which is calculating a gross margin % (gross margin/revenue), X3 is the gross margin % I want it to solve for that the user can specify in a cell in my model such as 40% and Y9 is the cell that I want it to solve for the value in.

    This code used to execute the goal seek previously but now every time I run it I get a 400 error message or a Compile Error message saying there is an invalid or unqualified reference. And it points to the .Range portion of the formula that is after GoalSeek Goal. How do I get past this error message, I'm new to VBA and am really confused as to how to fix.


    VBA Code:

    Sub SeekIt()

    Range("AC3").GoalSeek Goal:=.Range("X3").Value, ChangingCell:=.Range("Y9")

    End Sub


    I've googled this error and even tried using the below code but still got the same error messages

    For example:


    Sub SeekIt()
    With Worksheets("Sheet1")
    .Range("A1").GoalSeek Goal:=.Range("A2").Value, ChangingCell:=.Range("A3")
    End With
    With Worksheets("Sheet2")
    .Range("B1").GoalSeek Goal:=.Range("B2").Value, ChangingCell:=.Range("B3")
    End With
    End Sub


    Open to any feedback on how to fix this error message.

    Thanks!

  2. #2

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Goal Seek Macro Giving 400 Error or Compile Error: Invalid or Unqualified Referen

    Correction to my code shown above:

    Sub GoalSeek()

    Range("AC3").GoalSeek Goal:=Range("X3").Value, ChangingCell:=Range("Y9")

    End Sub

    When I run this, I now get Run time error '1004':

    Reference isn't valid.

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,217

    Re: Goal Seek Macro Giving 400 Error or Compile Error: Invalid or Unqualified Referen

    from testing it would appear that the formula is not valid, Y9 must be some part of the calculation
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Goal Seek Macro Giving 400 Error or Compile Error: Invalid or Unqualified Referen

    Actually I figured it out. The changing cell had to have a value in it for the macro to run. I just input a dummy amount of $1.00 in there and when I ran the macro it then worked. Leaving the changing cell blank causes the runtime error. Thanks for looking at this!

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width