0

I want to apply goal seek across several rows when there is a change to any cell in the work sheet. I want to apply this from row 7 to row 11. The first problem I have is that excel is crashing each time I run this. I am just starting to learn VBA so any help is much apreciated. Thank you!

My code is below:

Option Explicit

Private Sub Worksheet_Calculate()
CheckGoalSeek
End Sub

Private Sub CheckGoalSeek()
Range("T7").GoalSeek Goal:=0, ChangingCell:=Range("V7")
End Sub
Community
  • 1
  • 1
user2324441
  • 11
  • 1
  • 1
  • 3
  • What is in cell T7 and cell V7? – chuff Apr 26 '13 at 15:38
  • Cell T7 is breakage fee which is equal to the difference between the present value of a series of cash flows discounted at rate 'R' and the present value of a series of cash flows discounted at Treasury Rate plus a premium X. Cell V7 is the premium X. – user2324441 Apr 26 '13 at 16:09
  • 1
    Use the Worksheet_Change event. Looks like goal seek iteration is triggering a worksheet recalculation which is triggering a new goal seek ... – chuff Apr 26 '13 at 16:55

1 Answers1

2

You appear to be triggering an infinite loop: worksheet calculation -> goal seek calculation -> worksheet calculation -> ...

One option is to change the event that triggers the goal seek.

I would recommend the Worksheet_Change event. The event code would be the same except for the sub declaration, which would be Private Sub Worksheet_Change(ByVal Target As Range).

A simple For loop will perform the Goal Seek on the different rows:

 Option Explicit

 Private Sub CheckGoalSeek()
    Dim i as Long
    For i = 7 to 11
        Range("T"& i).GoalSeek Goal:=0, ChangingCell:=Range("V"& i)
    Next
 End Sub
chuff
  • 5,846
  • 1
  • 21
  • 26