Macro for automatic goal seek... again

[Midgles]

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??


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

 

[Midgles]

sly bump to keep it in view

 

[Midgles]

another bump.. hoping for help

 

[shg]

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.

 

[Midgles]

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]

Try this.

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]

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]

The code runs all the rows in the current selection.

 

[Midgles]

Doh.. egg on my face :D

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