Automatically Goal Seeking

[Moo1987]

Just wondering if anyone can help!?
If I post incorrectly apologises, I don't post much.

I have a page called Summary(first Tab), on that page Cell B96,97,98 have rates in which change.

when you change Cell B96 it changes Cell R117 to R108 on the Financial Calculations page (Tab 4)
I need Cell R117 to stay 0 and for the values to change accoringly to Cell R108 Then I need the value in C106 to change with the rate need to achieve this.

This is the same for Cell B97 and B98 on the first Tab

when you change Cell B97 it changes Cell R156 to R150 on the Financial Calculations page (Tab 4)
I need Cell R156 to stay 0 and for the values to change accoringly to Cell R150 Then I need the value in C148 to change with the rate need to achieve this.

when you change Cell B98 it changes Cell R196 to R192 on the Financial Calculations page (Tab 4)
I need Cell R196 to stay 0 and for the values to change accoringly to Cell R192 Then I need the value in C190 to change with the rate need to achieve this.

I hope this makes sense, help is very appreciated.
Thanks in advance.

 

[offthelip]

Yoiu haven't given us enough information to help you, because you haven't told us anything about how changing one cell will effect another cell. However if I have understood what you want to , i believe you want to iterate to a solution.
The usual way of doing this is by using a Do until statement. Assuming I am increment Cell B96 and the output of your calculation is in Cell R106 then this is the sort of code you need

Sub test()
s = 0
Do
 s = s + 0.001
 Cells(96, 2) = s ' Set Cell B96 to S
 
 reslt = Cells(106, 18) ' pick up results from cells R106
Loop Until reslt > 1




End Sub

 

[Moo1987]

Yeah, Sorry I thought my explanation might be a problem.

This hsould be alot easier, I hope!

I have a Macro that works fine, but I want it to do this automatically when Cells B96,97 and 98 are changed on the "Summary" sheet

Sub GoalSeeking()
'
' GoalSeeking Macro
'
' Keyboard Shortcut: Ctrl+g
'
    Sheets("Financial Calculations").Select
    ActiveWindow.SmallScroll Down:=90
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    Range("R117").GoalSeek Goal:=0, ChangingCell:=Range("R106")
    ActiveWindow.SmallScroll Down:=45
    Range("R156").GoalSeek Goal:=0, ChangingCell:=Range("R148")
    ActiveWindow.SmallScroll Down:=39
    Range("R196").GoalSeek Goal:=0, ChangingCell:=Range("R190")
    Sheets("Summary").Select
    Range("B96").Select
End Sub

Thanks In Advance

 

[offthelip]

That is very easy, put this subroutine in the Worksheet change event for the Summary sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(target, Range("B96:B98")) Is Nothing) Then
 Call goalseeking ()
End If


End Sub