Forum Index | Search | Register (New User) | Login (Existing User) | Who's Online | FAQ | Main Site

Technical Discussion
  >> Windows Issues Previous thread View all threads Next thread


Register (or login) on our website and you will not see this ad.


Pages in this thread: 1 | [2] | 3 | (show all)   Print Thread
Standard User ian72
(knowledge is power) Fri 16-May-14 08:02:36
Print Post

Re: Excel help


[re: deleted] [link to this post] 		 
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
Standard User ian72
(knowledge is power) Fri 16-May-14 08:05:38
Print Post

Re: Excel help


[re: ian72] [link to this post] 		 
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.
Standard User deleted
(deleted) Fri 16-May-14 08:28:02
Print Post

Re: Excel help


[re: ian72] [link to this post] 		 
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,


Register (or login) on our website and you will not see this ad.

Standard User ian72
(knowledge is power) Fri 16-May-14 08:38:31
Print Post

Re: Excel help


[re: deleted] [link to this post] 		 
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 wink

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.
Standard User ian72
(knowledge is power) Fri 16-May-14 08:54:12
Print Post

Re: Excel help


[re: ian72] [link to this post] 		 
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
Standard User deleted
(deleted) Fri 16-May-14 09:05:38
Print Post

Re: Excel help


[re: ian72] [link to this post] 		 
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)
Standard User ian72
(knowledge is power) Fri 16-May-14 09:16:42
Print Post

Re: Excel help


[re: deleted] [link to this post] 		 
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
Standard User deleted
(deleted) Fri 16-May-14 09:29:53
Print Post

Re: Excel help


[re: ian72] [link to this post] 		 
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)
Standard User ian72
(knowledge is power) Fri 16-May-14 09:43:15
Print Post

Re: Excel help


[re: deleted] [link to this post] 		 
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
Standard User deleted
(deleted) Fri 16-May-14 10:00:03
Print Post

Re: Excel help


[re: ian72] [link to this post] 		 
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?
Pages in this thread: 1 | [2] | 3 | (show all)   Print Thread

Previous thread View all threads Next thread
Jump to