+ Reply to Thread
Results 1 to 6 of 6

using Goal Seek inside a recorded macro:the field does not accommodate a context menu

  1. #1
    gvm
    Guest

    using Goal Seek inside a recorded macro:the field does not accommodate a context menu

    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?

  2. #2
    Max
    Guest

    re: using Goal Seek inside a recorded macro:the field does not accommodate a context menu

    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 <at>yahoo<dot>com
    ----
    "gvm" <[email protected]> wrote in message
    news:[email protected]...
    > 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?




  3. #3
    Rowan
    Guest

    re: using Goal Seek inside a recorded macro:the field does not accommodate a context menu

    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?


  4. #4
    Dana DeLouis
    Guest

    re: using Goal Seek inside a recorded macro:the field does not accommodate a context menu

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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?




  5. #5
    gvm
    Guest

    re: using Goal Seek inside a recorded macro:the field does not accommodate a context menu


    excellent, thanks Max, Rowan and Dana

  6. #6
    Max
    Guest

    re: using Goal Seek inside a recorded macro:the field does not accommodate a context menu

    You're welcome !
    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "gvm" <[email protected]> wrote in message
    news:[email protected]...
    >
    > excellent, thanks Max, Rowan and Dana




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1