Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
auto sort
I currently am working on a excel spreadsheet that needs to be a working
database. It is currently linked to bloomberg, (stock prices are automatically fed and updated) (live feed) The only column that will change everyday is the last one, although when the last one changes the positions of the stocks need to be sorted based on that last column in ascending order. can anyone assist me with this. I am on a deadline and have been working on this project for the last week, not which direction i should be going with this. (vlookup, macro) not sure. anyone's help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
auto sort
Right-click on the WS where you want to do your sort and paste this code into
the window that pops up: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim EndData As Long If Target.Column < 2 Then Exit Sub Application.ScreenUpdating = False EndData = Cells(Rows.Count, 1).End(xlUp).Row With Range(Cells(2, 1), Cells(EndData, 2)) .Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With Application.ScreenUpdating = False End Sub Be mindful of the fact that the current sort column is defined as: ..Sort Key1:=Range("B2") Change to suite your needs. Regards, Ryan--- -- RyGuy "Vicki" wrote: I currently am working on a excel spreadsheet that needs to be a working database. It is currently linked to bloomberg, (stock prices are automatically fed and updated) (live feed) The only column that will change everyday is the last one, although when the last one changes the positions of the stocks need to be sorted based on that last column in ascending order. can anyone assist me with this. I am on a deadline and have been working on this project for the last week, not which direction i should be going with this. (vlookup, macro) not sure. anyone's help would be greatly appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
auto sort
Thank you Ryan,
I am typing this in now the column that will determine the sort of the entire row is J and the columns start at 6 and go up. When I started typing OrderCustom:=1,MatchCase:=False Orentation:=xlTopToBottom I receive "compile error Expected: Expression" Thanks so much for your help. So what are the next steps after put the code? "ryguy7272" wrote: Right-click on the WS where you want to do your sort and paste this code into the window that pops up: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim EndData As Long If Target.Column < 2 Then Exit Sub Application.ScreenUpdating = False EndData = Cells(Rows.Count, 1).End(xlUp).Row With Range(Cells(2, 1), Cells(EndData, 2)) .Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With Application.ScreenUpdating = False End Sub Be mindful of the fact that the current sort column is defined as: .Sort Key1:=Range("B2") Change to suite your needs. Regards, Ryan--- -- RyGuy "Vicki" wrote: I currently am working on a excel spreadsheet that needs to be a working database. It is currently linked to bloomberg, (stock prices are automatically fed and updated) (live feed) The only column that will change everyday is the last one, although when the last one changes the positions of the stocks need to be sorted based on that last column in ascending order. can anyone assist me with this. I am on a deadline and have been working on this project for the last week, not which direction i should be going with this. (vlookup, macro) not sure. anyone's help would be greatly appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
auto sort
Hi Ryan,
So I cut and past the code and then I updated colum J's numbers and it didnt automatically sort. I'm sorry thanks for you patience "ryguy7272" wrote: Right-click on the WS where you want to do your sort and paste this code into the window that pops up: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim EndData As Long If Target.Column < 2 Then Exit Sub Application.ScreenUpdating = False EndData = Cells(Rows.Count, 1).End(xlUp).Row With Range(Cells(2, 1), Cells(EndData, 2)) .Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With Application.ScreenUpdating = False End Sub Be mindful of the fact that the current sort column is defined as: .Sort Key1:=Range("B2") Change to suite your needs. Regards, Ryan--- -- RyGuy "Vicki" wrote: I currently am working on a excel spreadsheet that needs to be a working database. It is currently linked to bloomberg, (stock prices are automatically fed and updated) (live feed) The only column that will change everyday is the last one, although when the last one changes the positions of the stocks need to be sorted based on that last column in ascending order. can anyone assist me with this. I am on a deadline and have been working on this project for the last week, not which direction i should be going with this. (vlookup, macro) not sure. anyone's help would be greatly appreciated. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
auto sort
Try the code below...remember, right-click on the tab of the worksheet that
you want the code to run in (this is a €˜Private subroutine, so it will only affect the sheet that you paste the code into, as opposed to affecting all the sheets in the entire workbook). Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim EndData As Long If Target.Column < 10 Then Exit Sub Application.ScreenUpdating = False EndData = Cells(Rows.Count, 1).End(xlUp).Row With Range(Cells(2, 1), Cells(EndData, 3)) .Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With Application.ScreenUpdating = False End Sub Two lines of code are important he Line #1: If Target.Column < 10 Then Exit Sub This tells Excel to do the sort after you finish with entering data into ColumnJ (i.e., when you reach ColumnK) Line #2: ..Sort Key1:=Range("B2"),€¦€¦..etc. This tells Excel to sort according to the data in ColumnB. If your data doesnt extend to ColumnJ, or extends past columnJ, change Line#1. Also, if you want to sort according to a different column, change this: Range("B2") Hope that helps. Write back if you have another question. Regards, Ryan--- -- RyGuy "Vicki" wrote: Hi Ryan, So I cut and past the code and then I updated colum J's numbers and it didnt automatically sort. I'm sorry thanks for you patience "ryguy7272" wrote: Right-click on the WS where you want to do your sort and paste this code into the window that pops up: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim EndData As Long If Target.Column < 2 Then Exit Sub Application.ScreenUpdating = False EndData = Cells(Rows.Count, 1).End(xlUp).Row With Range(Cells(2, 1), Cells(EndData, 2)) .Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With Application.ScreenUpdating = False End Sub Be mindful of the fact that the current sort column is defined as: .Sort Key1:=Range("B2") Change to suite your needs. Regards, Ryan--- -- RyGuy "Vicki" wrote: I currently am working on a excel spreadsheet that needs to be a working database. It is currently linked to bloomberg, (stock prices are automatically fed and updated) (live feed) The only column that will change everyday is the last one, although when the last one changes the positions of the stocks need to be sorted based on that last column in ascending order. can anyone assist me with this. I am on a deadline and have been working on this project for the last week, not which direction i should be going with this. (vlookup, macro) not sure. anyone's help would be greatly appreciated. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
auto sort
WHOOPS!! I posted too soon. I think you want this instead:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim EndData As Long If Target.Column < 10 Then Exit Sub Application.ScreenUpdating = False EndData = Cells(Rows.Count, 1).End(xlUp).Row With Range(Cells(2, 1), Cells(EndData, 10)) .Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With Application.ScreenUpdating = False End Sub This is just like the last post, except for this line: With Range(Cells(2, 1), Cells(EndData, 10)) The €˜10 makes all data sort over to ColumnJ, which Im sure is what you want (I think the last sample just sorted the date up until ColumnC). Regards, Ryan--- -- RyGuy "ryguy7272" wrote: Try the code below...remember, right-click on the tab of the worksheet that you want the code to run in (this is a €˜Private subroutine, so it will only affect the sheet that you paste the code into, as opposed to affecting all the sheets in the entire workbook). Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim EndData As Long If Target.Column < 10 Then Exit Sub Application.ScreenUpdating = False EndData = Cells(Rows.Count, 1).End(xlUp).Row With Range(Cells(2, 1), Cells(EndData, 3)) .Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With Application.ScreenUpdating = False End Sub Two lines of code are important he Line #1: If Target.Column < 10 Then Exit Sub This tells Excel to do the sort after you finish with entering data into ColumnJ (i.e., when you reach ColumnK) Line #2: .Sort Key1:=Range("B2"),€¦€¦..etc. This tells Excel to sort according to the data in ColumnB. If your data doesnt extend to ColumnJ, or extends past columnJ, change Line#1. Also, if you want to sort according to a different column, change this: Range("B2") Hope that helps. Write back if you have another question. Regards, Ryan--- -- RyGuy "Vicki" wrote: Hi Ryan, So I cut and past the code and then I updated colum J's numbers and it didnt automatically sort. I'm sorry thanks for you patience "ryguy7272" wrote: Right-click on the WS where you want to do your sort and paste this code into the window that pops up: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim EndData As Long If Target.Column < 2 Then Exit Sub Application.ScreenUpdating = False EndData = Cells(Rows.Count, 1).End(xlUp).Row With Range(Cells(2, 1), Cells(EndData, 2)) .Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With Application.ScreenUpdating = False End Sub Be mindful of the fact that the current sort column is defined as: .Sort Key1:=Range("B2") Change to suite your needs. Regards, Ryan--- -- RyGuy "Vicki" wrote: I currently am working on a excel spreadsheet that needs to be a working database. It is currently linked to bloomberg, (stock prices are automatically fed and updated) (live feed) The only column that will change everyday is the last one, although when the last one changes the positions of the stocks need to be sorted based on that last column in ascending order. can anyone assist me with this. I am on a deadline and have been working on this project for the last week, not which direction i should be going with this. (vlookup, macro) not sure. anyone's help would be greatly appreciated. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
auto sort
Hi Ryan! I just submitted another message because i was still confused I, I
will delete it and try your directions again, Thanks for your time. Have a great weekend. "ryguy7272" wrote: Try the code below...remember, right-click on the tab of the worksheet that you want the code to run in (this is a €˜Private subroutine, so it will only affect the sheet that you paste the code into, as opposed to affecting all the sheets in the entire workbook). Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim EndData As Long If Target.Column < 10 Then Exit Sub Application.ScreenUpdating = False EndData = Cells(Rows.Count, 1).End(xlUp).Row With Range(Cells(2, 1), Cells(EndData, 3)) .Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With Application.ScreenUpdating = False End Sub Two lines of code are important he Line #1: If Target.Column < 10 Then Exit Sub This tells Excel to do the sort after you finish with entering data into ColumnJ (i.e., when you reach ColumnK) Line #2: .Sort Key1:=Range("B2"),€¦€¦..etc. This tells Excel to sort according to the data in ColumnB. If your data doesnt extend to ColumnJ, or extends past columnJ, change Line#1. Also, if you want to sort according to a different column, change this: Range("B2") Hope that helps. Write back if you have another question. Regards, Ryan--- -- RyGuy "Vicki" wrote: Hi Ryan, So I cut and past the code and then I updated colum J's numbers and it didnt automatically sort. I'm sorry thanks for you patience "ryguy7272" wrote: Right-click on the WS where you want to do your sort and paste this code into the window that pops up: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim EndData As Long If Target.Column < 2 Then Exit Sub Application.ScreenUpdating = False EndData = Cells(Rows.Count, 1).End(xlUp).Row With Range(Cells(2, 1), Cells(EndData, 2)) .Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With Application.ScreenUpdating = False End Sub Be mindful of the fact that the current sort column is defined as: .Sort Key1:=Range("B2") Change to suite your needs. Regards, Ryan--- -- RyGuy "Vicki" wrote: I currently am working on a excel spreadsheet that needs to be a working database. It is currently linked to bloomberg, (stock prices are automatically fed and updated) (live feed) The only column that will change everyday is the last one, although when the last one changes the positions of the stocks need to be sorted based on that last column in ascending order. can anyone assist me with this. I am on a deadline and have been working on this project for the last week, not which direction i should be going with this. (vlookup, macro) not sure. anyone's help would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a way to Auto sort or save a sort? | Excel Discussion (Misc queries) | |||
Auto Sort | Setting up and Configuration of Excel | |||
auto sort | Excel Discussion (Misc queries) | |||
Auto sort | Excel Worksheet Functions | |||
auto sort? | Excel Discussion (Misc queries) |