Macro for automatic goal seek... again

Midgles

New Member
Joined
Jan 27, 2011
Messages
6
Hi Everyone, I know there are a few threads for this already, but I have a specific question that doesn't seem to be covered, rather than dragging up old threads that could get messy, I thought I would start a new one.

I found some script on this forum similar to the one below (just tweeked the locations a bit to make it fit my sheet).

The sheet I use it on, is a pricing tool, that lets people calculate a sale price based on a % Gross margin, however this could be anywhere from 30 lines, to 3000 lines. As it is fairly complex, the goal seek can take a while when doing more than 300 or so lines, so i would like to give the user the option of specifying a start and end row for the goal seek. (it always uses the same column, it's just the rows that will vary) so for example, they could specify the option of performing the goal seek on rows 220 through 880 if they wanted, without having to go in and change the script (as I am doing currently when anyone needs to use it)

Anyone able to help?? :):confused:


Sub MultipleGoalSeekA()
Dim aPercentage As Variant, aCell As Range

aPercentage = Application.InputBox("Enter target percentage:" & vbCr & _
"(for 10% enter 10, for 0.5% enter 0.5)", Type:=1)
If aPercentage = False Then Exit Sub
aPercentage = aPercentage / 100

On Error Resume Next 'This prevents an error at blank cells
For Each aCell In Range("U3:U303")
aCell.GoalSeek Goal:=aPercentage, ChangingCell:=aCell.Offset(0, -11)
Next aCell
End Sub
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,816
Office Version
  1. 2010
Platform
  1. Windows
All you need are simple formulas.

If you evaluate GM as the fraction of gross receipts that exceeed direct cost, then price = cost / (1-GM), e.g., for GM = 25%, price = 1.33* cost.
 
Last edited:

Midgles

New Member
Joined
Jan 27, 2011
Messages
6

ADVERTISEMENT

it's not a simple formula thing.. there are way too many variables involved..

The GM is actually shown at a % of net sales (with rebates, discounts, costs, % overheads blah blah blah) which is why goal seek is needed (many of the % used are % of the sale price)

the goal seek works great, I just want to be able to add the option for the user to chose which group of rows to run it on
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,816
Office Version
  1. 2010
Platform
  1. Windows
Try this.

Code:
Sub MultipleGoalSeekA()
    Dim vPct      As Variant
    Dim cell      As Range
 
    vPct = Application.InputBox(Prompt:="Enter target percentage:" & vbLf & _
                                        "(for 10% enter 10, for 0.5% enter 0.5)", _
                                Type:=1)
    If vPct = False Then Exit Sub
    vPct = vPct / 100
 
    For Each cell In Intersect(Selection.EntireRow, Columns("U"), ActiveSheet.UsedRange)
        If cell.HasFormula Then
            cell.GoalSeek Goal:=vPct, _
                          ChangingCell:=cell.Offset(0, -11)
        End If
    Next cell
End Sub
 

Midgles

New Member
Joined
Jan 27, 2011
Messages
6

ADVERTISEMENT

Thanks, but now that only runs for the current row :(



it's this part i want to improve..

For Each aCell In Range("U3:U303")

this currently runs the goal seek on the first 300 rows of the sheet, but i want the user to be able to chose how many rows to run it on.. i.e. the first 50, or the first 500.. or even on rows 400 to 850 etc etc etc...

The people using this are people that have trouble with basic formulae, let alone tweeking the VB script. I just want to make it nice and idiot proof..

click button, enter % say which rows.. done.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,816
Office Version
  1. 2010
Platform
  1. Windows
The code runs all the rows in the current selection.
 

Midgles

New Member
Joined
Jan 27, 2011
Messages
6
Doh.. egg on my face :D

Thanks, i never thought to highlight the cells first! thats Awesome! thanks a bunch :)
 

Forum statistics

Threads
1,144,373
Messages
5,723,980
Members
422,529
Latest member
mbilal429

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top