Solved

using Goal Seek in VBA

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

912 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

17 Experts available now in Live!

Get 1:1 Help Now