?
Solved

using Goal Seek in VBA

Posted on 2016-07-22
5
Medium Priority
?
541 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

762 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