Goal Seek with cell value as variable name

[SuedeGopher]

Howdy minds of MrExcel, long time reader, first time poster.

Normally I find the droid I am looking for with the handy search feature, but this time I'm coming up empty. My skills are pretty rudimentary and I'm often not able to combine ideas like I'd like to. End of sob story, beginning of begging for help!

I am trying to set up a statement with a goal seek. There are currently three different variables I'd like to use as my basis, all with the same seek cell and the same cell to change. Basically I have a drop down box and the value to be sought, then I'm trying to have my statement read the contents of the cell and use that name to know where to point to. Here's what I built that works like not at all:

 Sub GSeek()
    With Worksheets("Inputs")
        Dim ChngCell As String
        Dim ROA As Range
        Dim Payment As Range
        Dim Yield As Range
        ChngCell = Range("M40")
        Set ROA = Range("N35").Value
        Set Payment = Range("B16").Value
        Set Yield = Range("C14").Value
                
        ROA.GoalSeek _
        Goal:=.Range("N40").Value, _
        ChangingCell:=.Range("B15")
    End With
End Sub

So in that example, I manually set the Goal Seek value to ROA, but I'd like to be able to be able to read off of my drop down box to decide what range to set.

Does that all make sense and if so, who want some good Karma for helping an idiot!?!

 

[shg]

What's in N40 and B15?

 

[SuedeGopher]

What's in N40 and B15?

N40 is a user defined value (say 1.30) and B15 is a rate factor used to calculate cash flow. It's something that already works from a manual basis, I just want to automate it so the user does not have to select one of the three defined fields prior to performing the goal seek.

 

[shg]

OK, backing up, you declare and initialize a bunch of variables and then don't use them.

Can you explain what you're trying to do?

 

[SuedeGopher]

OK, backing up, you declare and initialize a bunch of variables and then don't use them.

Can you explain what you're trying to do?

Probably a case where including code has done more harm than good.

I'm effectively trying to read the contents of a dropdown box and have that inform the location of the cell to use in a goalseek. In the code above, that is where I have ROA.Goalseek. The other two inputs for goalseek are static, it's the location of the initial reference cell that I'm trying to have as a variable. User selects "ROA", goalseek should use a different range than if user had selected "Payment" or "Yield." Does that make sense?

I appreciate you answering and trying to interpret my madness!</SPAN>

 

[shg]

Maybe ...

Sub GopherSeek()
    ' Assume that

    '   o   N40 contains a dropdown with the name of a single cell range that
    '       contains the goal, and 

    '   o   B15 contains a dropdown with the name of a single cell range (the
    '       changing cell), then 

    '   o   Goalseek M40 to the value of the cell referenced by N40 by changing
    '       the cell referenced by B15

    With Worksheets("Inputs")
        .Range("M40").GoalSeek Goal:=.Range(.Range("N40").Value), _
                               ChangingCell:=.Range(.Range("B15").Value)
    End With
End Sub

If that doesn't work, please rewrite the assumptions.

 

[SuedeGopher]

I wrote it out another way using If/Then/Else...and it worked! Not as elegant perhaps, but at least illustrates what I was going for:

Sub GSeek()
    With Worksheets("Inputs")
        If Range("M40") = "Yield" Then
            Range("C14").GoalSeek Goal:=Range("N40"), ChangingCell:=Range("B15")
        ElseIf Range("M40") = "ROA" Then
            Range("N35").GoalSeek Goal:=Range("N40"), ChangingCell:=Range("B15")
        Else
            Range("B16").GoalSeek Goal:=Range("N40"), ChangingCell:=Range("B15")
        End If
    End With
End Sub

 

[shg]

Good job!

You need a . in front of each of those Range functions in order to use the reference established by the With statement.