Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2015
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Apply Goal Seek to Entire column

    I need to run goal seek through an entire column of data and I'm sure its probably not that hard, but my VBA skills are very limited. Below is an example of what I would like to do, any help is appreciated.

    I basically want to calculate the breakeven price for products based on our shipping and other product costs and I want to change our retail price to the point where profit is 0 (breakeven).

    I want to set column AQ (formula for profit) to be 0 by changing column N (retail price). I want to run this through all rows in the worksheet for these columns. The macro I have for a single cell is below, I'm just not sure on the code to write to loop the goal seek to continue until there is no more data in the column.



    Range("AQ2").GoalSeek Goal:=0, ChangingCell:=Range("N2")
    End Sub

    Thanks!

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    fishnet,

    place in a standard module:
    Code:
    Public Sub GoalSeeker()
    For I = 2 To 30
        Cells(I, 43).GoalSeek Goal:=0, ChangingCell:=Cells(I, 14)
    Next I
    End Sub
    Change the 30 to the number of the last row

    HTH,
    Maud

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    fishnet

    ..welcome to the Forum.
    Maud has a great solution.
    But if you change your columns, you'll have to remember to change the vba code.

    You could use this
    Code:
    Public Sub GoalSeeker()
    For I = 2 To 30
        Cells(I, "AQ").GoalSeek Goal:=0, ChangingCell:=Cells(I, "N")
    Next I
    End Sub
    You will still have to change the code if your columns change, but it may be easier to spot.

    zeddy

Posting Permissions

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