Advanced onsheet Goal Seek

[fredlo2010]

Hi guys,

This something I have always wanted to do and I think I can do it now with VBA.

I have this sheet with some data and formulas. What I want is to update my formulas as i enter values to fit my needs.

To make it simpler if i have a price for apples and I want to know whats the price for 15 apples or how many apples I can buy with $ 15.00.

I could use the regular Goal seek but my formulas contain several If and they are a little complicated. It is not just simple multiplication.

I found this piece of code online and it looks very promising. But i cannot make it fit my needs. Maybe I am just trying the wrong things, or maybe this is extremely complicated.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Row = 6 And Target.Column = 2 Then

Target.Value = vbNullString
Range("B5").FormulaR1C1 = "=r[1]c/r[-1]c"

End If

End Sub



I made a small dummy sheet to get some ideas.

Thanks a lot guys.

8331

[bolekblues]

Hi

Have you tried solver add-in? If that is just an optimization task, I don't think vba is needed

[snb]

What is complicated about:


Cells(7, 2) = -([B6] + 25) / (10 ^ -4) * [B5]

[Kenneth Hobs]

I have used this for a sheet event. Selecting a cell in column J cause the value in that row's column A to change until the goal in that row's column I is found from the formula in column H.

For example in row 7:
A=7.2
B=1.01
C=2.654
D==ROUND(100/((100-A7)/C7+A7/B7),3)
E=2.531
F=96.5
G==ROUND(F7*D7/100,3)
H==ROUND(100-F7/((100-A7)/C7+A7/B7)/E7*(100-A7),1)
I=16

e.g. If I7=18 then A7=8.2.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Long
If Target.Count > 1 Or Target.Column <> 10 Then Exit Sub
r = Target.Row
Application.EnableEvents = False
On Error Resume Next
Range("H" & r).GoalSeek Goal:=Range("I" & r).Value, ChangingCell:=Range("A" & r)
Application.EnableEvents = True
End Sub