Solved

using Goal Seek in VBA

Posted on 2016-07-22
5
34 Views
Last Modified: 2016-08-06
I'm trying to add Goal Seek to a little VBA because I want to use on multiple cells and not have to enter the changing value in each of them individually.

If the value of Q1 changes, then the value of Q13 through AB13 should change to match the value of Q1
AND
use Goal Seek to find Q11 through AB11

I'm trying the following which is NOT working:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$Q$1" Then

Sub GoalSeek()
Range(“Q13:AB13”).GoalSeek Goal:=.Range("$Q$1").Value, _ ChangingCell:=Range(“$Q11:$AB$11”)
End Sub

End If

End Sub

Open in new window


I don't ever remember working with Excel VBA before.  Can anyone help me correct this?

Thanks so much!
0
Comment
Question by:fabi2004
  • 4
5 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41725378
First, you can only use Goal Seek on one target cell at a time.

Second, the cells Q13:AB13 need to contain formulas for the Goal Seek function to work.  If you first change them to match the value in Q1, then the Goal Seek function will error.

Third, you state that if Q1 is changed, then you want Q13:AB13 to be changed to match Q1.  THEN, your goalseek function is also trying to change Q11:AB11 to also match the value of Q1.  If Q11:AB11 contain formulas referencing Q13:AB13, then this will never work.

Do you have an example file showing the structure so that we can be of more help?

Regards,
Glenn
0
 
LVL 1

Author Comment

by:fabi2004
ID: 41727893
Hi Glenn,

I wish I'd seen your comment Friday, I would have responded much sooner.  Sorry for the delay.

I worked on this all weekend with no luck at all.  I found a few things online that "should" work, but don't in this case.

I'm attaching the workbook.  Please ignore the links.  Basically, an Exec wants to be able to manipulate a random number of cells in row 13 (they will always be consecutive and the value will be the same for all cells involved)  in the AR worksheet for forecasting purposes.  Changing the value in a cell on row 13 should change the value on the corresponding cell in the same column on row 11.  Which in turn automatically changes row 12.  This works great using the Goal Seek feature IF we only look at one column, but I need to find a way that he can input a value for a cell in row 13 and the value spread across all columns moving forward to the end of the data and then 'goal seek" row 11.

I'm afraid my explanation sounds more complicated than it needs to be.  If you simply use Goal Seek on any column for the cell row 13 and 11, you should see the effect.

Again, I've found one or two VBA codes online that seem like they should work, but they error out.  I can provide those if you'd like to see.

I REALLY appreciate any help with this.
2017-forecast-with-DSO.xlsx
0
 
LVL 1

Author Comment

by:fabi2004
ID: 41728305
This works on a single iteration of Goal Seek:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$1" Then

Range("K13").GoalSeek goal:=Range("F1"), changingcell:=Range("K11")


End If
End Sub

Open in new window


So, if I want to set up a loop so the code continues executing for each consecutive column, L, M, N..., then do I set up a variable for the column name and use something like 'i+1'$13 for the range?
0
 
LVL 1

Accepted Solution

by:
fabi2004 earned 0 total points
ID: 41728537
I think I got this working.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$1" Then

Dim iCol As Long
  
  For iCol = Columns("K").Column To Columns("AB").Column
    With Columns(iCol)
      .Cells(13).GoalSeek Goal:=Range("F1"), ChangingCell:=.Cells(11)
    End With
  Next iCol


End If
End Sub

Open in new window


I'm going to leave the question up another day or so to make sure I don't run into problems with it.
0
 
LVL 1

Author Closing Comment

by:fabi2004
ID: 41745352
I figured it out on my own.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now