Results 1 to 3 of 3
  1. #1
    rainer
    Guest

    Goal Seek - for a column of cells

    I want to find what value in column B will result in column L = 10.
    I use Goal Seek to set the cell in column L, To Value = 10 and changing cell is the cell in column B (same row).
    I want to do rows 4 through nn (lots of rows).

    I can

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Goal Seek - for a column of cells

    Hi Rainer,

    The following macro will do multiple row Goal seeking. It is more or less geared to your particular example but could be made more generic if required. If you select the first changing cell (B3 in your example) and run the macro it will enter a value in all non blank cells underneath, so it is important to have some value in the range before running the macro. You could just fill the range with 1. <pre>Sub RangeGoalSeek()
    Application.ScreenUpdating = False
    Dim x As Integer
    Dim NumRows As Integer
    Dim cCell As String, vCell As String
    Range(Selection, Selection.End(xlDown)).Select
    NumRows = Selection.Rows.Count
    For x = 0 To NumRows - 1
    cCell = ActiveCell.Offset(x, 0).Address
    vCell = ActiveCell.Offset(x, 10).Address
    Range(vCell).GoalSeek Goal:=10, ChangingCell:=Range(cCell)
    Next x
    Application.ScreenUpdating = True
    End Sub</pre>

    I have included it in your sample workbook and am attaching same. I have included a macro button which you can use to run the example.

    Hope it helps

    Andrew C
    Attached Files Attached Files

  3. #3
    rainer
    Guest

    Re: Goal Seek - for a column of cells

    Wow - What a one step solution - for me. Thank you.
    I will take some time to study this and learn from it.

    Rainer

Posting Permissions

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