PDA

View Full Version : GoalSeek by VBA



r_know
07-29-2013, 01:27 AM
Hi,

Normally in formula or VBA code, we apply GoalSeek Goal:= NUMERICAL VALUE Ie. 0 or any numerical.

How do I give a any cell reference to my goal numerical instead of manually write value?

Reg, RL>

Kenneth Hobs
07-29-2013, 05:42 AM
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

r_know
07-29-2013, 10:15 AM
I do not understand this; In my sheet GoalSeek Goal in D14 and Changing Cell G7.

Pls can you explain little, how I can apply!

Kenneth Hobs
07-29-2013, 11:18 AM
You did not provide enough information so I posted my best guess. My example is worksheet event code. As one selects a cell in column 10 in a row, it executed data from that row for a goal seek.

The line that pertains to your need is:

Range("H" & r).GoalSeek Goal:=Range("I" & r).Value, ChangingCell:=Range("A" & r)
Since you did not say, I will assume that the value is set in A1. My code for you then becomes:

Range("A1").GoalSeek Goal:=Range("D14").Value, ChangingCell:=Range("G7")
Macro code from a recording would be close to this.

r_know
07-31-2013, 08:49 PM
Yes, It work!
Many Thanks!
RL>