0

I have a Goal Seek Macro:

Sub GOALSEEK()
    Range("K25").GOALSEEK Goal:=0, ChangingCell:=Range("K10")
End Sub

That works for 2015Q4, cell K25 at 0%.

I want to adapt the macro for moving Goal and Seek variables.

The moving Goal will be stored in C25 (0, 0.01, 0.05, etc), and the moving Seek will be dependent on C6 (Q2, Q3, Q4, which will map to I25, J25, K25, respectively).

enter image description here

EDIT

After some tinkering, I just discovered that the moving Goal can be implemented by changing Goal:=0 to Goal:=Range("Cell"):

Sub GOALSEEK()
    Range("K25").GOALSEEK Goal:=Range("C25"), ChangingCell:=Range("K10")
End Sub

Still don't know how to make Seek move.

pnuts
  • 58,317
  • 11
  • 87
  • 139
emehex
  • 9,874
  • 10
  • 54
  • 100
  • Do you have a question? Did you have a question, figure it out, then post your answer as your EDIT? – FreeMan Apr 02 '15 at 15:50
  • I was just tinkering after I posted the question and figured out how to adjust the Goal. But I still don't know how to adjust the moving Seek. – emehex Apr 02 '15 at 15:51
  • See if this will help: http://stackoverflow.com/questions/16240176/automatic-goal-seek-over-range-of-cells – FreeMan Apr 02 '15 at 15:54
  • How do you decide which cell to perform the seek on? I can think of a couple ways to do this. – Kyle Apr 02 '15 at 16:01
  • @Kyle. The seeks will be for Q2, Q3, Q4 as decided/picked in cell C6 (and concatenated in C7 to match I9, J9, K9). If Q3, K25 and K10 will change to J25 and J10. – emehex Apr 02 '15 at 16:05

2 Answers2

1

Put some conditional logic in your code like below to make the decision on the fly based on which quarter is entered in cell C6. 

Sub GOALSEEK()

Dim gseek, chngcell as Range

If Range("C6") = "Q2" Then
   Set gseek = Range("I25")
   Set chngcell = Range("I10")
Elseif Range("C6") = "Q3" Then
   Set gseek = Range("J25")
   Set chngcell = Range("J10")
Elseif Range("C6") = "Q4" Then
   Set gseek = Range("K25")
   Set chngcell = Range("K10")
End If

gseek.GOALSEEK Goal:=Range("C25"), ChangingCell:=chngcell

End Sub

You could further this to set another range variable to the cell you want to change as well. Let me know if this doesn't work for you.

emehex
  • 9,874
  • 10
  • 54
  • 100
Kyle
  • 2,543
  • 2
  • 16
  • 31
1

You may try the following approach:

Define new named range call it for example SeekCell and in Refers To enter:

=INDEX($I$25:$K$25,1,MATCH("2015"&$C$6,$I$9:$K$9))

Define new named range call it for example ChangeCell and in Refers To enter:

=INDEX($I$10:$K$10,1,MATCH("2015"&$C$6,$I$9:$K$9))

Define new named range call it for example GoalCell and in Refers To enter:

=$C$25

In the VBA code use:

Range("SeekCell").GOALSEEK  Goal:=Range("GoalCell"), ChangingCell:=Range("ChangeCell")
BrakNicku
  • 5,935
  • 3
  • 24
  • 38