using Goal Seek inside a recorded macro
 

I'm trying to record a macro that automates the use of GoalSeek. My procedure
starts by 1) start macro recording, 2) go to Tools menu and invoke Goal Seek,
3) set the target cell by clicking on the appropriate cell, and 4) is where I
have a problem. The need is to enter a value from a cell in the spreadsheet
but it appears the value must be manually read and then typed in via the
keyboard. For instance, the field does not accommodate a context menu thus
the opportunity to copy and paste into it is blocked. Is there are way around
this?

Adapting from a post by Myrna,
perhaps something along these lines ..

Assuming we have in Sheet1,

In C1: =SUM(A1:B1)
where A1 contains say, a constant: 100
B1 is the changing cell

The goal seek target for C1 will be input in C2

The sub below will run the goal seek for C1
based on the input in C2 to yield the required value in B1

Private Sub CommandButton1_Click()
Sheets("Sheet1").Select
Target = Range("C2").Value
Range("C1").Select
Range("C1").GoalSeek Goal:=Target, ChangingCell:=Range("B1")
End Sub

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"gvm" wrote in message
...
I'm trying to record a macro that automates the use of GoalSeek. My
procedure
starts by 1) start macro recording, 2) go to Tools menu and invoke Goal
Seek,
3) set the target cell by clicking on the appropriate cell, and 4) is
where I
have a problem. The need is to enter a value from a cell in the
spreadsheet
but it appears the value must be manually read and then typed in via the
keyboard. For instance, the field does not accommodate a context menu thus
the opportunity to copy and paste into it is blocked. Is there are way
around
this?

Not all macro operations can be recorded. Assume I am wanting to use GoalSeak
on Cell D1, setting the value to be the same as that of cell A1 by changing
cell B1 the code would look like this:

Sub GlSeak()
Range("D1").GoalSeek Goal:=Range("A1").Value _
, ChangingCell:=Range("B1")
End Sub

Hope this helps
Rowan
"gvm" wrote:

I'm trying to record a macro that automates the use of GoalSeek. My procedure
starts by 1) start macro recording, 2) go to Tools menu and invoke Goal Seek,
3) set the target cell by clicking on the appropriate cell, and 4) is where I
have a problem. The need is to enter a value from a cell in the spreadsheet
but it appears the value must be manually read and then typed in via the
keyboard. For instance, the field does not accommodate a context menu thus
the opportunity to copy and paste into it is blocked. Is there are way around
this?

Hi. You're so close. One technique is to record your macro like you are
doing. Then, go back and edit your macro.
You can set your "Goal" based on another cell by taking the Value of that
cell (D1 in this example)

Sub Demo()
'// Recorded Macro
Range("A1").GoalSeek Goal:=100, ChangingCell:=Range("B1")

'// Make your changes here...
Range("A1").GoalSeek Goal:=Range("D1"), ChangingCell:=Range("B1")
End Sub

HTH :)
--
Dana DeLouis
Win XP & Office 2003


"gvm" wrote in message
...
I'm trying to record a macro that automates the use of GoalSeek. My
procedure
starts by 1) start macro recording, 2) go to Tools menu and invoke Goal
Seek,
3) set the target cell by clicking on the appropriate cell, and 4) is
where I
have a problem. The need is to enter a value from a cell in the
spreadsheet
but it appears the value must be manually read and then typed in via the
keyboard. For instance, the field does not accommodate a context menu thus
the opportunity to copy and paste into it is blocked. Is there are way
around
this?

excellent, thanks Max, Rowan and Dana

You're welcome !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"gvm" wrote in message
...

excellent, thanks Max, Rowan and Dana