• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1353
  • Last Modified:

using Goal Seek in VBA

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
fabi2004
Asked:
fabi2004
  • 4
1 Solution
 
Glenn RayExcel VBA DeveloperCommented:
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
 
fabi2004CIOAuthor Commented:
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
 
fabi2004CIOAuthor Commented:
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
 
fabi2004CIOAuthor Commented:
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
 
fabi2004CIOAuthor Commented:
I figured it out on my own.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now