Goal Seek with VBA no reacting to change in value

[skorpio911]

I am working on an excel model. I have successfully planted a VBA code for automatic calculation, but it only works if I change the targeted value manually (which in actuality is based on a calculation from another sheet).

Long story short, VBA-coded Goal Seek is not reacting to an automatic change in targeted value by manipulating some other variables in another sheet as it is linked.

I am using the following code. Please help me out. Thank you!


Private Sub Worksheet_Change(ByVal Target As Range)

Dim inputCells As Range

'List all input cells
Set inputCells = Range("MaxPowerOutput, NoOfTurbines, RequiredPower")

'Run the macro if an input cell changes
If Not Application.Intersect(Range(Target.Address), inputCells) Is Nothing Then

'Run the Goal Seek using the values in the NoOfTurbines

Range("MaxPowerOutput").GoalSeek Goal:=Range("RequiredPower"), ChangingCell:=Range("NoOfTurbines")

End If

End Sub

 

[offthelip]

Welcome to the forum,
the worksheet change event is only triggered when you manually change a cell ( as you have found) so I suggest you put your code into the worksheet_calculate event instead. This should trigger when a change is made from another sheet, provided the cell that changes is in the recalculate loop. ( if it isn't you just need to add any calculation that includes that cell on that sheet)
Note the worksheet caculate doesn't have the "target" as parameter so you need to deal with that separately. the way I would deal with this is save the value in your "target" cell in a globla variable and then check to see if it has changed on each recalculate.
PS when posting code can you please use the code formatting button which formats the code , ( click the VBA icon)
like this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim inputCells As Range

'List all input cells
Set inputCells = Range("MaxPowerOutput, NoOfTurbines, RequiredPower")

'Run the macro if an input cell changes
If Not Application.Intersect(Range(Target.Address), inputCells) Is Nothing Then

'Run the Goal Seek using the values in the NoOfTurbines

Range("MaxPowerOutput").GoalSeek Goal:=Range("RequiredPower"), ChangingCell:=Range("NoOfTurbines")

End If

End Sub

 

[skorpio911]

Thank you for your response and guidance on using the worksheet_calculate. However, I am not able to understand the solution you provided for the absence of the target variable in worksheet_calculate. Can you please elaborate on this?

 

[offthelip]

Here is a simple demonstration of what I am talking about.

In new workbook with blank worksheets Sheet1 and Sheet 2
Put the number 5 in Cell A1 of sheet 2
Then in sheet 1 put this equation in A1

=Sheet2!A1

This references sheets 2 so it changes when sheet 2 is changed

Put this equation in B2 on sheet 1,

=A1*2

this will force a recalculation when the value in A1 changes,

Now you should have 5 in A1 , 10 in B1 and blank in C1

Then put this code in the worksheet code for sheet 1.

Public A1saved As Variant
Private Sub Worksheet_Calculate()
If Cells(1, 1) <> A1saved Then
Cells(1, 3) = Cells(1, 3) + 1
A1saved = Cells(1, 1)
End If
End Sub

Now change the value in sheet 2 A1 to 6

Look at sheet 1 and the values are now 6, 12 and 1
Note the count in C3 is just there to show the code has been run, you don't need that for your application, it is there so you can check that the code doesn't run on worksheet change event . ie try changing any other cell on sheet 1 or 2 and nothing happens.