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
Solved

using Goal Seek in VBA

Posted on 2016-07-22
5
141 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
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.

839 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