gtd526
Well-known Member
- Joined
- Jul 30, 2013
- Messages
- 673
- Office Version
- 2019
- Platform
- Windows
Hello,
I want to sort by G/L % (L:L), it sorts correctly but the sort begins in row 13, when it should be line 9. How can I make the sort start on line 9?
Thank you.
Here is the data Im working with:
Here is the Macro:
I want to sort by G/L % (L:L), it sorts correctly but the sort begins in row 13, when it should be line 9. How can I make the sort start on line 9?
Thank you.
Here is the data Im working with:
Cell Formulas | ||
---|---|---|
Range | Formula | |
G9:G26 | G9 | =IF($F9=0,0,MAX($F9,$G9)) |
H9:H26 | H9 | =IF($H9=0,$F9,MEDIAN($H9,$F9,0)) |
I13:I26,I9:I10 | I9 | =(E9*D9) |
J9:J26 | J9 | =F9*D9 |
K9:K26 | K9 | =J9-I9 |
L9:L26 | L9 | =IF(K9<>0,K9/I9,"") |
M9:M26 | M9 | =IF(I9<>0,I9/$I$27,"") |
P9:P26 | P9 | =IF(OR($N9="",$N9="sold"),"",$N9-TODAY()) |
Q9:Q26 | Q9 | =IF(OR($N9="",$N9="sold"),"","day(s)") |
F13:F26 | F13 | =VLOOKUP($A13,$A$53:$B$68,2,0) |
B9:B26 | B9 | =IF(COUNTIF(Sectors!$A$3:$AA$50,A9)=1,INDEX(Sectors!$A$2:$AA$2,MAX((Sectors!$A$3:$AA$50=A9)*(COLUMN(Sectors!$A$2:$AA$2)))),"") |
I27:K27 | I27 | =SUM(I9:I26) |
L27 | L27 | =IFERROR((K27/I27),"") |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
D9:D26 | Cell Value | ="put" | text | NO |
D9:D26 | Cell Value | ="call" | text | NO |
B9:B26 | Expression | =$N9="sold" | text | NO |
B9:B26 | Expression | =MOD(ROW(),2)=1 | text | NO |
A9:A26 | Expression | =$N9="sold" | text | NO |
A9:A26 | Expression | =MOD(ROW(),2)=1 | text | NO |
C9:C26 | Cell Value | ="put" | text | NO |
C9:C26 | Cell Value | ="call" | text | NO |
I9:O26,C9:E26 | Expression | =$N9="sold" | text | NO |
F9:F26,H9:H26 | Expression | =$F9=$H9 | text | NO |
F9:G26 | Expression | =$F9=$G9 | text | NO |
I9:O26,C9:E26 | Expression | =MOD(ROW(),2)=1 | text | NO |
L27 | Cell Value | <0 | text | NO |
L27 | Cell Value | >0 | text | NO |
K27 | Cell Value | <0 | text | NO |
K27 | Cell Value | >0 | text | NO |
D1,K9:L26 | Cell Value | <0 | text | NO |
D1,K9:L26 | Cell Value | >0 | text | NO |
K8:L8 | Expression | =AVERAGE(K9:K26)<0 | text | NO |
K8:L8 | Expression | =AVERAGE(K9:K26)>0 | text | NO |
Here is the Macro:
VBA Code:
Sub BearMkt_Sort_by_Gain()
Workbooks("The Whole Enchilada.xlsm").Worksheets("Bear Mkt").Select
Range("A9:Q26").Select
ActiveWorkbook.Worksheets("Bear Mkt").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Bear Mkt").Sort.SortFields.Add2 Key:=Range("L9:L26") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Bear Mkt").Sort
.SetRange Range("A9:Q26")
' .Header = xlNo
' .MatchCase = False
' .Orientation = xlTopToBottom
' .SortMethod = xlPinYin
.Apply
End With
Range("A9").Select
End Sub