Frontier Line!!! Please help with macro

Status
Not open for further replies.

j33pguy

Well-known Member
Joined
Aug 6, 2004
Messages
633
hi,
i have 5000 lines of Risk versus Cost data....
column A is 1-5000
column B is Risk numbers
Column C is cost numbers

what i need to figure out how to do is graph the frontier line (optimal line) through these points! any ideas as what macro i would need?
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

colbymack

Active Member
Joined
Jul 14, 2005
Messages
333
I am not sure if this is the same as what you are looking for, but if you right click on the chart (I think you have to do it on one of the charted items (i.e. on a bar within a bar graph), you can add a Trendline. You can also display the formula for that trendline.

HTH,
Colbymack
 

j33pguy

Well-known Member
Joined
Aug 6, 2004
Messages
633
no, that's not what i need.
i just wrote this macro....it seems to do what i need....but it's very slow....
any ideas as how to make it faster? :)
i would appreciate any help!

Code:
Sub Macro1()

   XX = 0
   Do Until XX = 30

   Application.ScreenUpdating = False
    Range("A3:C3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Sort Key1:=Range("B3"), Order1:=xlDescending, Key2:=Range("C3") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]>=R[1]C[-1],0,1)"
    Range("D3").Select
    Selection.Copy
    Range("D4").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A3:D3").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWindow.LargeScroll Down:=-1
    Range("D5939").Select
    Selection.End(xlUp).Select
    Range("D3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.End(xlUp).Select
    Range("E3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A3:E3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("E3"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
       JJ = 3
    Do Until Range("E" & JJ) = 1
    If Range("E" & JJ) = 0 Then
    Range("E" & JJ).Select
    Selection.EntireRow.Delete
    JJ = JJ + 1
    
    End If
    
    Loop
    
    XX = XX + 1
    Loop
    Application.ScreenUpdating = True
End Sub
 

colbymack

Active Member
Joined
Jul 14, 2005
Messages
333
If you haven't already done so, check out the post by Buffalo titled

excel program for the "critical line algorithm"?

It has some links that might be useful.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,497
Office Version
  1. 365
Platform
  1. Windows
Regarding speeding up your code, you have a lot of unnecessary Select statements in your code. Selecting ranges slows down your code, and most of the time it is not necessary to select them to work on them.

Here are some examples in your code where you can get rid of Select statements and combine two lines of code into one:

Range("D3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]>=R[1]C[-1],0,1)"

becomes
Range("D3").FormulaR1C1 = "=IF(RC[-1]>=R[1]C[-1],0,1)"

Range("D3").Select
Selection.Copy

becomes
Range("D3").Copy

Range("D5939").Select
Selection.End(xlUp).Select

becomes
Range("D5939").End(xlUp).Select

Range("E" & JJ).Select
Selection.EntireRow.Delete

becomes
Range("E" & JJ).EntireRow.Delete
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,119,144
Messages
5,576,336
Members
412,718
Latest member
dragosm
Top