Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting VBA problem
Please help!
The problem code is as follows:: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'Change the ranges if required If Intersect(Target, Range("D14:P20,B29:P35") Is Nothing Then Exit Sub Application.EnableEvents = False Application.ScreenUpdating = False On Error GoTo GetOut Range("D21:F21,I21:K21,N21:P21,D36:F21,I36:K21,N36 :P21").Copy Range("T29:V21").PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("B10,G10,L10,B25,G25,L25").Copy Destination:=Range("S29") Application.CutCopyMode = False Range("S29:V34").Sort Key1:=Range("S29"), _ Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlLeftToRight Target.Offset(0, 1).Select GetOut: Application.EnableEvents = True Application.ScreenUpdating = True End Sub I received and adapted another code to fit my range. The code is not working. I'm trying to automaticly sort three sumtotals in different cell references (D21:P21 etc) to the relevant names (B10,G10 etc) in a different location on the same sheet. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting VBA problem
Your code is confused - you have ranges stated backwards (I36:K21 is usually given I21:K36), you
have overlapping ranges of different size: for example, this will error: Range("D21:F21,I21:K21,N21:P21,D36:F21,I36:K21,N36 :P21").Copy but if rewritten this way it won't error: Range("D21:F36,I21:K36,N21:P36").Copy and then you paste values over a previous paste range (Range("T29:V21").PasteSpecial ), sort based on an incomplete range, etc. It would be better if you described in words what you want. Try to be specific. HTH, Bernie MS Excel MVP "wynand" wrote in message ... Please help! The problem code is as follows:: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'Change the ranges if required If Intersect(Target, Range("D14:P20,B29:P35") Is Nothing Then Exit Sub Application.EnableEvents = False Application.ScreenUpdating = False On Error GoTo GetOut Range("D21:F21,I21:K21,N21:P21,D36:F21,I36:K21,N36 :P21").Copy Range("T29:V21").PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("B10,G10,L10,B25,G25,L25").Copy Destination:=Range("S29") Application.CutCopyMode = False Range("S29:V34").Sort Key1:=Range("S29"), _ Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlLeftToRight Target.Offset(0, 1).Select GetOut: Application.EnableEvents = True Application.ScreenUpdating = True End Sub I received and adapted another code to fit my range. The code is not working. I'm trying to automaticly sort three sumtotals in different cell references (D21:P21 etc) to the relevant names (B10,G10 etc) in a different location on the same sheet. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting VBA problem
The data is score tables on one sheet.
B10-F10, G10-K10, L10-P10, B25-F25,G25-K25, and L25-P25 are the names of individuals. cells are merged (b10-f10 etc) and =6 individuals B14-F20,G14-F20, L14-P20,B29-F35,G29-K35 and L29-P35 is the scores. three of these scores are sumtotaled in D21-F21, I21-K21, N21-P21, D36-F36,I36-K36 and n36-p36. The automatic sorting should take place in S29 S34 (Names of individuals)and T29 to v34 (three sumtotals of individuals) When number or score is changed in the range the data is updated and sorted per scores automatically Usually this is done manually by coppying and pasting names in cells (S) and then pasting the sumtotals in T and then everything is highlighted and sorted by preference of the totals. A recored macro does not shorten the process E.g. Jack John 111 111 111 100 50 111 111 110 99 10 111 111 111 100 50 111 111 110 99 10 222 200 100 220 198 20 Jack 222 100 50 John 250 110 20 etc... "Bernie Deitrick" wrote: Your code is confused - you have ranges stated backwards (I36:K21 is usually given I21:K36), you have overlapping ranges of different size: for example, this will error: Range("D21:F21,I21:K21,N21:P21,D36:F21,I36:K21,N36 :P21").Copy but if rewritten this way it won't error: Range("D21:F36,I21:K36,N21:P36").Copy and then you paste values over a previous paste range (Range("T29:V21").PasteSpecial ), sort based on an incomplete range, etc. It would be better if you described in words what you want. Try to be specific. HTH, Bernie MS Excel MVP "wynand" wrote in message ... Please help! The problem code is as follows:: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'Change the ranges if required If Intersect(Target, Range("D14:P20,B29:P35") Is Nothing Then Exit Sub Application.EnableEvents = False Application.ScreenUpdating = False On Error GoTo GetOut Range("D21:F21,I21:K21,N21:P21,D36:F21,I36:K21,N36 :P21").Copy Range("T29:V21").PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("B10,G10,L10,B25,G25,L25").Copy Destination:=Range("S29") Application.CutCopyMode = False Range("S29:V34").Sort Key1:=Range("S29"), _ Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlLeftToRight Target.Offset(0, 1).Select GetOut: Application.EnableEvents = True Application.ScreenUpdating = True End Sub I received and adapted another code to fit my range. The code is not working. I'm trying to automaticly sort three sumtotals in different cell references (D21:P21 etc) to the relevant names (B10,G10 etc) in a different location on the same sheet. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting VBA problem
Which column will you use to sort the resulting table? S (names) T, U, or V (scores)? Your code
shows sorting based on names, but that doesn't seem like it would change, so there would be little need to do that. Jack 222 100 50 John 250 110 20 etc... The only descending column is V - is that what you want? HTH, Bernie MS Excel MVP "wynand" wrote in message ... The data is score tables on one sheet. B10-F10, G10-K10, L10-P10, B25-F25,G25-K25, and L25-P25 are the names of individuals. cells are merged (b10-f10 etc) and =6 individuals B14-F20,G14-F20, L14-P20,B29-F35,G29-K35 and L29-P35 is the scores. three of these scores are sumtotaled in D21-F21, I21-K21, N21-P21, D36-F36,I36-K36 and n36-p36. The automatic sorting should take place in S29 S34 (Names of individuals)and T29 to v34 (three sumtotals of individuals) When number or score is changed in the range the data is updated and sorted per scores automatically Usually this is done manually by coppying and pasting names in cells (S) and then pasting the sumtotals in T and then everything is highlighted and sorted by preference of the totals. A recored macro does not shorten the process E.g. Jack John 111 111 111 100 50 111 111 110 99 10 111 111 111 100 50 111 111 110 99 10 222 200 100 220 198 20 Jack 222 100 50 John 250 110 20 etc... "Bernie Deitrick" wrote: Your code is confused - you have ranges stated backwards (I36:K21 is usually given I21:K36), you have overlapping ranges of different size: for example, this will error: Range("D21:F21,I21:K21,N21:P21,D36:F21,I36:K21,N36 :P21").Copy but if rewritten this way it won't error: Range("D21:F36,I21:K36,N21:P36").Copy and then you paste values over a previous paste range (Range("T29:V21").PasteSpecial ), sort based on an incomplete range, etc. It would be better if you described in words what you want. Try to be specific. HTH, Bernie MS Excel MVP "wynand" wrote in message ... Please help! The problem code is as follows:: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'Change the ranges if required If Intersect(Target, Range("D14:P20,B29:P35") Is Nothing Then Exit Sub Application.EnableEvents = False Application.ScreenUpdating = False On Error GoTo GetOut Range("D21:F21,I21:K21,N21:P21,D36:F21,I36:K21,N36 :P21").Copy Range("T29:V21").PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("B10,G10,L10,B25,G25,L25").Copy Destination:=Range("S29") Application.CutCopyMode = False Range("S29:V34").Sort Key1:=Range("S29"), _ Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlLeftToRight Target.Offset(0, 1).Select GetOut: Application.EnableEvents = True Application.ScreenUpdating = True End Sub I received and adapted another code to fit my range. The code is not working. I'm trying to automaticly sort three sumtotals in different cell references (D21:P21 etc) to the relevant names (B10,G10 etc) in a different location on the same sheet. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting VBA problem
First T then U and then V. The names should sort with the totals when values
are changed in the tables. In other words this is a template sheet, but individuals' scores would change and therefore their ranking as per S,T,U an V with the names. Changes in scores would the rankings with all associated data. "Bernie Deitrick" wrote: Which column will you use to sort the resulting table? S (names) T, U, or V (scores)? Your code shows sorting based on names, but that doesn't seem like it would change, so there would be little need to do that. Jack 222 100 50 John 250 110 20 etc... The only descending column is V - is that what you want? HTH, Bernie MS Excel MVP "wynand" wrote in message ... The data is score tables on one sheet. B10-F10, G10-K10, L10-P10, B25-F25,G25-K25, and L25-P25 are the names of individuals. cells are merged (b10-f10 etc) and =6 individuals B14-F20,G14-F20, L14-P20,B29-F35,G29-K35 and L29-P35 is the scores. three of these scores are sumtotaled in D21-F21, I21-K21, N21-P21, D36-F36,I36-K36 and n36-p36. The automatic sorting should take place in S29 S34 (Names of individuals)and T29 to v34 (three sumtotals of individuals) When number or score is changed in the range the data is updated and sorted per scores automatically Usually this is done manually by coppying and pasting names in cells (S) and then pasting the sumtotals in T and then everything is highlighted and sorted by preference of the totals. A recored macro does not shorten the process E.g. Jack John 111 111 111 100 50 111 111 110 99 10 111 111 111 100 50 111 111 110 99 10 222 200 100 220 198 20 Jack 222 100 50 John 250 110 20 etc... "Bernie Deitrick" wrote: Your code is confused - you have ranges stated backwards (I36:K21 is usually given I21:K36), you have overlapping ranges of different size: for example, this will error: Range("D21:F21,I21:K21,N21:P21,D36:F21,I36:K21,N36 :P21").Copy but if rewritten this way it won't error: Range("D21:F36,I21:K36,N21:P36").Copy and then you paste values over a previous paste range (Range("T29:V21").PasteSpecial ), sort based on an incomplete range, etc. It would be better if you described in words what you want. Try to be specific. HTH, Bernie MS Excel MVP "wynand" wrote in message ... Please help! The problem code is as follows:: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'Change the ranges if required If Intersect(Target, Range("D14:P20,B29:P35") Is Nothing Then Exit Sub Application.EnableEvents = False Application.ScreenUpdating = False On Error GoTo GetOut Range("D21:F21,I21:K21,N21:P21,D36:F21,I36:K21,N36 :P21").Copy Range("T29:V21").PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("B10,G10,L10,B25,G25,L25").Copy Destination:=Range("S29") Application.CutCopyMode = False Range("S29:V34").Sort Key1:=Range("S29"), _ Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlLeftToRight Target.Offset(0, 1).Select GetOut: Application.EnableEvents = True Application.ScreenUpdating = True End Sub I received and adapted another code to fit my range. The code is not working. I'm trying to automaticly sort three sumtotals in different cell references (D21:P21 etc) to the relevant names (B10,G10 etc) in a different location on the same sheet. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting VBA problem
The easiest thing to do is to set up your data table with links to the values of interest - BUT use
absolute addressing. When you sort, the links stay with the intended cell after the sort. So use, for example: =$B$10 NOT =B10 for all the links, then when you sort, you'll be fine. Record a macro performing the sorting that you want, and you'll be able to use the exact code that your record. HTH, Bernie MS Excel MVP "wynand" wrote in message ... First T then U and then V. The names should sort with the totals when values are changed in the tables. In other words this is a template sheet, but individuals' scores would change and therefore their ranking as per S,T,U an V with the names. Changes in scores would the rankings with all associated data. "Bernie Deitrick" wrote: Which column will you use to sort the resulting table? S (names) T, U, or V (scores)? Your code shows sorting based on names, but that doesn't seem like it would change, so there would be little need to do that. Jack 222 100 50 John 250 110 20 etc... The only descending column is V - is that what you want? HTH, Bernie MS Excel MVP "wynand" wrote in message ... The data is score tables on one sheet. B10-F10, G10-K10, L10-P10, B25-F25,G25-K25, and L25-P25 are the names of individuals. cells are merged (b10-f10 etc) and =6 individuals B14-F20,G14-F20, L14-P20,B29-F35,G29-K35 and L29-P35 is the scores. three of these scores are sumtotaled in D21-F21, I21-K21, N21-P21, D36-F36,I36-K36 and n36-p36. The automatic sorting should take place in S29 S34 (Names of individuals)and T29 to v34 (three sumtotals of individuals) When number or score is changed in the range the data is updated and sorted per scores automatically Usually this is done manually by coppying and pasting names in cells (S) and then pasting the sumtotals in T and then everything is highlighted and sorted by preference of the totals. A recored macro does not shorten the process E.g. Jack John 111 111 111 100 50 111 111 110 99 10 111 111 111 100 50 111 111 110 99 10 222 200 100 220 198 20 Jack 222 100 50 John 250 110 20 etc... "Bernie Deitrick" wrote: Your code is confused - you have ranges stated backwards (I36:K21 is usually given I21:K36), you have overlapping ranges of different size: for example, this will error: Range("D21:F21,I21:K21,N21:P21,D36:F21,I36:K21,N36 :P21").Copy but if rewritten this way it won't error: Range("D21:F36,I21:K36,N21:P36").Copy and then you paste values over a previous paste range (Range("T29:V21").PasteSpecial ), sort based on an incomplete range, etc. It would be better if you described in words what you want. Try to be specific. HTH, Bernie MS Excel MVP "wynand" wrote in message ... Please help! The problem code is as follows:: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'Change the ranges if required If Intersect(Target, Range("D14:P20,B29:P35") Is Nothing Then Exit Sub Application.EnableEvents = False Application.ScreenUpdating = False On Error GoTo GetOut Range("D21:F21,I21:K21,N21:P21,D36:F21,I36:K21,N36 :P21").Copy Range("T29:V21").PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("B10,G10,L10,B25,G25,L25").Copy Destination:=Range("S29") Application.CutCopyMode = False Range("S29:V34").Sort Key1:=Range("S29"), _ Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlLeftToRight Target.Offset(0, 1).Select GetOut: Application.EnableEvents = True Application.ScreenUpdating = True End Sub I received and adapted another code to fit my range. The code is not working. I'm trying to automaticly sort three sumtotals in different cell references (D21:P21 etc) to the relevant names (B10,G10 etc) in a different location on the same sheet. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting VBA problem
thank you it works!
"Bernie Deitrick" wrote: The easiest thing to do is to set up your data table with links to the values of interest - BUT use absolute addressing. When you sort, the links stay with the intended cell after the sort. So use, for example: =$B$10 NOT =B10 for all the links, then when you sort, you'll be fine. Record a macro performing the sorting that you want, and you'll be able to use the exact code that your record. HTH, Bernie MS Excel MVP "wynand" wrote in message ... First T then U and then V. The names should sort with the totals when values are changed in the tables. In other words this is a template sheet, but individuals' scores would change and therefore their ranking as per S,T,U an V with the names. Changes in scores would the rankings with all associated data. "Bernie Deitrick" wrote: Which column will you use to sort the resulting table? S (names) T, U, or V (scores)? Your code shows sorting based on names, but that doesn't seem like it would change, so there would be little need to do that. Jack 222 100 50 John 250 110 20 etc... The only descending column is V - is that what you want? HTH, Bernie MS Excel MVP "wynand" wrote in message ... The data is score tables on one sheet. B10-F10, G10-K10, L10-P10, B25-F25,G25-K25, and L25-P25 are the names of individuals. cells are merged (b10-f10 etc) and =6 individuals B14-F20,G14-F20, L14-P20,B29-F35,G29-K35 and L29-P35 is the scores. three of these scores are sumtotaled in D21-F21, I21-K21, N21-P21, D36-F36,I36-K36 and n36-p36. The automatic sorting should take place in S29 S34 (Names of individuals)and T29 to v34 (three sumtotals of individuals) When number or score is changed in the range the data is updated and sorted per scores automatically Usually this is done manually by coppying and pasting names in cells (S) and then pasting the sumtotals in T and then everything is highlighted and sorted by preference of the totals. A recored macro does not shorten the process E.g. Jack John 111 111 111 100 50 111 111 110 99 10 111 111 111 100 50 111 111 110 99 10 222 200 100 220 198 20 Jack 222 100 50 John 250 110 20 etc... "Bernie Deitrick" wrote: Your code is confused - you have ranges stated backwards (I36:K21 is usually given I21:K36), you have overlapping ranges of different size: for example, this will error: Range("D21:F21,I21:K21,N21:P21,D36:F21,I36:K21,N36 :P21").Copy but if rewritten this way it won't error: Range("D21:F36,I21:K36,N21:P36").Copy and then you paste values over a previous paste range (Range("T29:V21").PasteSpecial ), sort based on an incomplete range, etc. It would be better if you described in words what you want. Try to be specific. HTH, Bernie MS Excel MVP "wynand" wrote in message ... Please help! The problem code is as follows:: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'Change the ranges if required If Intersect(Target, Range("D14:P20,B29:P35") Is Nothing Then Exit Sub Application.EnableEvents = False Application.ScreenUpdating = False On Error GoTo GetOut Range("D21:F21,I21:K21,N21:P21,D36:F21,I36:K21,N36 :P21").Copy Range("T29:V21").PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("B10,G10,L10,B25,G25,L25").Copy Destination:=Range("S29") Application.CutCopyMode = False Range("S29:V34").Sort Key1:=Range("S29"), _ Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlLeftToRight Target.Offset(0, 1).Select GetOut: Application.EnableEvents = True Application.ScreenUpdating = True End Sub I received and adapted another code to fit my range. The code is not working. I'm trying to automaticly sort three sumtotals in different cell references (D21:P21 etc) to the relevant names (B10,G10 etc) in a different location on the same sheet. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Problem | Excel Discussion (Misc queries) | |||
Sorting problem . . . | Excel Discussion (Misc queries) | |||
Sorting problem | Excel Discussion (Misc queries) | |||
Sorting problem | Excel Discussion (Misc queries) | |||
Sorting problem | Excel Worksheet Functions |