|
|
|
Cells A36 to cell A50 have names in B36 to B50 have their values (which are imported for other cells in worksheet).
The values change and I want to automatically sort them by value highest to lowest
What's the best way to do this?
|
|
|
Only way I can think to do this is via a macro.
Be careful though is doing autorun macros wrongly could leave you with a very unresponsive spreadsheet.
Right click on the sheet tab and select "View Code". Paste this code in:
| Text | 1
23
45
67
89
1011
1213
1415
1617
1819
2021
2223
2425
26 | Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will ' cause an alert when they are changed.
Set KeyCells = Range("B36:B50")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then
' Sort cells.
Range("A36:B50").Select ActiveWorkbook.Worksheets("Sheet1").AutoFilter.sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.sort.SortFields.Add Key:=Range _ ("B36:B50"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.sort
.Header = xlYes .MatchCase = False
.Orientation = xlTopToBottom .SortMethod = xlPinYin
.Apply End With
End If
End Sub |
|
|
|
I must be doing something wrong as can't get it to work.
PS thanks for taking the time to help.
Edited by deleted (Thu 15-May-14 16:56:36)
|
|
Register (or login) on our website and you will not see this ad.
|
|
|
What version of Excel? I did it in 2010 but would think it should be ok in others.
The other thing you can do is record a macro that selects the data set and does the sort. Then copy that macro and copy it into the code provided here replacing the MsgBox line.
That way you will have the right code for doing the sort and by building it yourself on your spreadsheet it should bypass any issues (that was how I created it in the first place).
PS - failing that I can send you contact details and you could send me the spreadsheet and I could check it out and see if I can see where the problem is.
Edited by ian72 (Thu 15-May-14 17:01:13)
|
|
|
|
Thanks, It's a multi tab when I view code it says sheet 2, do I have to change it on the macro?
|
|
|
|
Yes. If the tab is not named sheet1 then you will need to change those references in the code to whatever the tab is called. And whilst I think of it if the code isn't running at all you may need to save as a macro enabled spreadsheet (2010 and above increased security for macros).
|
|
|
The sheet is called 2014 and I activated macros (the excel icon changed) I'm using 2010
Can you see any errors I made?
| 1
23
45
67
89
1011
1213
1415
1617
1819
2021
2223
24 | Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("B36:B50")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
' Sort cells.
Range("A36:B50").Select
ActiveWorkbook.Worksheets("2014").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("2014").AutoFilter.Sort.SortFields.Add Key:=Range _
("B36:B50"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("2014").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
End Sub |
|
|
|
Sorry, when I created the code I had setup a filter and the code was doing a sort based on that. To do it without a filter give this a try (it should work as my test spreadsheet is now setup about the same as yours):
| Text | 1
23
45
67
89
1011
1213
1415
1617
1819
2021
2223
2425
26 | Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will ' cause an alert when they are changed.
Set KeyCells = Range("B36:B50")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then
' Sort cells. CurrCell = ActiveCell.Address
Range("A36:B50").Select ActiveWorkbook.Worksheets("2014").sort.SortFields.Clear
ActiveWorkbook.Worksheets("2014").sort.SortFields.Add Key:=Range("B36:B50"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("2014").sort .SetRange Range("A36:B50")
.Header = xlGuess .MatchCase = False
.Orientation = xlTopToBottom .SortMethod = xlPinYin
.Apply End With
Range(CurrCell).Select End If
End Sub |
Edited by ian72 (Fri 16-May-14 07:03:35)
|
|
|
Another question in case that last option doesn't work... What is actually in the cells in the B column? Is it actual numbers or is it formulas pointing to other cells. If it is the latter then the sort won't work because the formulas won't move in the sort. It also won't trigger as the cell doesn't change if it is a formula just the value that is in it.
So, if it doesn't work can you post a sample of what is in a cell (say the formula/contents of B36).
EDIT : OK, have found a way to potentially do it with formulas but it could be clunky depending on the spreadsheet. Hopefully you don't have formulas in the column but if you do then we can try something a little more complicated.
Edited by ian72 (Thu 15-May-14 23:25:10)
|
|
|
|
Currently
=$B$18 is in cell B36 =$B$19 in B37 down to =$B$32 in cell B50
|
|
|
OK, that means the cell isn't changing because the cell is the formula not the value. So, what we need to do is to change it so that we monitor the source cells instead. Do B18..B32 have actual values rather than formulas? If so, then this should work:
| Text | 1
23
45
67
89
1011
1213
14 | Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range ' The variable KeyCells contains the cells that will ' cause an alert when they are changed.
Set KeyCells = Range("B18:B32") If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then
' Sort cells. CurrCell = ActiveCell.Address Range("A36:B50").Select ActiveWorkbook.Worksheets("2014").sort.SortFields.Clear
ActiveWorkbook.Worksheets("2014").sort.SortFields.Add Key:=Range("B36:B50"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("2014").sort .SetRange Range("A36:B50")
.Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin
.Apply End With Range(CurrCell).Select End If
End Sub |
|
|
|
|
PS. Also did a little tweak so that it returns you back to the cell you were in after doing the sort to stop it leaving you outside of where you stranded in the table.
|
|
|
B18.B32 also have formulas in.
It is the same ss you helped a while ago
In A18 to A32 I have the store name in alphabetical order and the values in B18 to B32, the values form a pie chart.
Under it I have the same info but want it sorted by value
Sorry I should of explained all this at the start,
|
|
|
We can do this one of 2 ways. I can give you a potential pointer (which is below) and you can see if you can sort it out or we can arrange for you to get me a copy of the spreadsheet and I could do it for you.
So, here is option 1 
On line 4 of the code above is the line
| Text | 1
| Set KeyCells = Range("B18:B32") |
This is the range of cells that it is watching for any changes. Only if these cells change will it resort (otherwise it will try and sort the spreadsheet every time anything changes which is likely to get annoying).
This has to point to something that has actual values in it rather than forumlas. So, if you change the range that it is pointing to to the source of the data (ie possibly A1:D6 going by the previous thread but might be bigger than that) then whenever the valuies change there they will change the results in B18:B32 which will change the values in B36:B50 and the change of source would trigger the sort (hopefully, as of course part of the problem could be the speed/order it all gets done in).
See if you can sort that, if not I am happy to have a look at the spreadsheet itself.
|
|
|
I think I have reproduced your spreadsheet functionally and suspect the range is A1:X15.
If so, then this would be the code (and it works on my version so hopefully will on yours):
| Text | 1
23
45
67
89
1011
1213
1415
1617
1819
2021
2223
2425
26 | Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will ' cause an alert when they are changed.
Set KeyCells = Range("A1:X15") CurrCell = ActiveCell.Address
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then ' Sort cells.
Range("A36:B50").Select ActiveWorkbook.Worksheets("2014").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("2014").Sort.SortFields.Add Key:=Range("B36:B50"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("2014").Sort .SetRange Range("A36:B50")
.Header = xlGuess .MatchCase = False
.Orientation = xlTopToBottom .SortMethod = xlPinYin
.Apply End With
Range(CurrCell).Select End If
End Sub |
|
|
|
Thanks
Got nearly right, now have the ss sorting but it runs low at top to high at bottom, any way I can revere that?
Edited by deleted (Fri 16-May-14 09:06:09)
|
|
|
Sounds good. The sort order should be set by line 15:
| Text | 1
| SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal |
Change xlAscending to xlDescending:
| Text | 1
| SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal |
|
|
|
I thought it might be that, but when I change it it goes to the macro and highlights line 14 and 15 in yellow. After pressing Debug
Edited by deleted (Fri 16-May-14 09:31:51)
|
|
|
This is the full code working on mine - try pasting it in in place of the current code.
| Text | 1
23
45
67
89
1011
1213
1415
1617
1819
2021
2223
2425
26 | Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will ' cause an alert when they are changed.
Set KeyCells = Range("A1:X15") CurrCell = ActiveCell.Address
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then ' Sort cells.
Range("A36:B50").Select ActiveWorkbook.Worksheets("2014").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("2014").Sort.SortFields.Add Key:=Range("B36:B50"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("2014").Sort .SetRange Range("A36:B50")
.Header = xlGuess .MatchCase = False
.Orientation = xlTopToBottom .SortMethod = xlPinYin
.Apply End With
Range(CurrCell).Select End If
End Sub |
|
|
|
|
Works great, thanks for all your help.
With your help I've learnt a better understanding of how formulas work and now much better at using them.
But I think a marco is a step to far for me.
BTW is it normal when using a macro that you can't use the undo feature?
|
|
|
|
You're welcome, it would be nice if this could be done without a macro but I can't find anything that suggests you can.
A quick google shows that Excel "clears the undo stack" after running a macro and seems there is no way to change that. Unfortunate side effect (I assume this is a performance thing).
|
|
|
I better not make a mistake when entering data 
Thanks again Ian.
|
|
|
|
The only other way to do it would actually to not use the code on change but add a button with the sort code and manually do it when you want an update - but this largely defeats the purpose of what you seem to be trying to achieve (ie might as well just set a filter on it and sort as and when needed rather than use a macro).
|
|
|
And another possible trick that might help with undo.
If you are doing a lot of changes you could add a bit of code that allows you to put a value in a particular cell that if it is set stops the sort code from running (and then turn it back on just before you change the last entry so the last change triggers it.
The new test is:
If Range("E18").Value <> "Y" Then
If I put a Y in the cell E18 on the 2014 sheet then the sort won't run and undos will stay available. Removing the Y from the cell (or setting it to anything else) will mean sort will run when the source table changes. If you want to put it somewhere else on the sheet then change E18 to whatever cell you put the Y in (or if you want to use something other than Y then change the Y to whatever you would rather use.
| Text | 1
23
45
67
89
1011
1213
1415
1617
1819
2021
2223
2425
2627
2829
30 | Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will ' cause an alert when they are changed.
If Range("E18").Value <> "Y" Then
Set KeyCells = Range("A1:X15") CurrCell = ActiveCell.Address
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then ' Sort cells.
Range("A36:B50").Select ActiveWorkbook.Worksheets("2014").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("2014").Sort.SortFields.Add Key:=Range("B36:B50"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("2014").Sort .SetRange Range("A36:B50")
.Header = xlGuess .MatchCase = False
.Orientation = xlTopToBottom .SortMethod = xlPinYin
.Apply End With
Range(CurrCell).Select End If
End If
End Sub |
|
|
|
If you don't want to use macros, there's a very easy way to achieve what you're after, if you're prepared to be flexible with where the data gets entered.
http://www.camieabz.co.uk/files/sorting.xlsx
Basically, you would shift the data entry area elsewhere, then in the area where you want the 'sorted data' to be displayed do the following (my spreadsheet's references used):
=LARGE($B$250:$B$258,1)
=LARGE($B$250:$B$258,2)
and so on until all your numerical values are listed where you want them.
Then in the 'names' column, you want
=INDEX($A$250:$B$258,MATCH($B1,$B$250:$B$258,0),1)
This throws back the name next to each value. There might be an issue with duplicate values, so that's likely in this solution, it's probably not going to be workable.
|
|
|
Forgot to add.
I would use the macro if macros were an acceptable route (if it's to be shared with others, sometimes macros are not the best option).
What's more, I tend to allocate an entire sheet for all data entry and another for working, and then pull data from it for the reading sheets. There are several advantage to this:
1. It separates the data entry (the area where mistakes will be made) from the calculation and working data. The latter might be locked or even hidden, to prevent accidental or malicious fiddling.
2. It makes it easier for users to split in one direction or another when editing it in future.
3. It reduces the time spent error checking. No need to double check the working page, if all the data entry is elsewhere.
4. Having 'reading sheets' makes for printing, chart exporting and so on, and if you want to copy and paste a read sheet because it would be good for another idea, you don't copy all the formulas, data entry and the rest.
I don't use macros too often, but if I do, I separate them into several steps in my head. I might want to have a spreadsheet go to named range, in a named sheet, look for the first entry, and if it's a '1', do further steps, but if it's anything but a '1' stop.
So then you break into "how do I goto named sheet?", "how do I goto named range?", and so on. Sometimes it's trial and error and I have seen 20 mini-macros, all doing one step each, then I pull the code from each, and make them into one.
There is masses of help online for pretty much every macro idea you might have, so you don't have to be a VB expert. All you need is to know what you want to do, and how to get it across to the VB folk in such forums, and stress that you don't want to be bamboozled with VB stuff.
|
|
|
|
Thanks Cammy, I think I will stick with macro, it's a personal ss so will be fine.
As I don't enter that much info at a time, going wrong isn't that big of deal, I can either correct it or close SS and start again.
|
|
|
I don't do many changes, so losing the "undo" isn't a great loss.
I'm a happy bunny with what you sorted for me.
|
|
|
Trying to add another marco.
I've altered the original macro to cells A30:B40 and works.
I'm now trying to add another macro for cells A43:B53
I've tried copying macro, but think I need to change the name of second marco but can't seem to do it?
Any ideas? (anyone)
Edited by deleted (Mon 05-Jan-15 10:15:37)
|
|
|
|
Found out that I couldn't have two separate macros , had to combine them into one.
Now working as I want it too.
|
Pages in this thread: 
Print Thread
deleted
