| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chabnging macros to functions
I have the following macro to sort a range of cells :-
Range("A2:J5").Select Range("J2").Activate Selection.Sort Key1:=Range("J2"), Key2:=Range("I2"), Key3:=Range("G2"), Order1:=xlDescending, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom That works fine when attahced to a button, bit I wanted it to work automatically when I update a second sheet that drives formulas on the original sheet. I'm using the "Private Sub worksheet_Calculate()" event to drive it, but using the same code as above gives me a Range selection error. Anyone any ideas how to get round this? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chabnging macros to functions
Richard,
It is always a good idea to fully identify the ranges by sheet when operating between sheets. Re-write like so: With Worksheets("SheetName") .Range("A2:J5").Sort Key1:=.Range("J2"), Key2:=.Range("I2"), Key3:=.Range("G2"), _ Order1:=xlDescending, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With HTH, Bernie MS Excel MVP "RichardG" wrote in message ... I have the following macro to sort a range of cells :- Range("A2:J5").Select Range("J2").Activate Selection.Sort Key1:=Range("J2"), Key2:=Range("I2"), Key3:=Range("G2"), Order1:=xlDescending, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom That works fine when attahced to a button, bit I wanted it to work automatically when I update a second sheet that drives formulas on the original sheet. I'm using the "Private Sub worksheet_Calculate()" event to drive it, but using the same code as above gives me a Range selection error. Anyone any ideas how to get round this? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chabnging macros to functions
Thanks for that, it works. Now a smaller secondary problem.
I have a total of 8 groups to sort, all on the same sheet and all consecutive. The first group will sort, but the sort for the second grouping, which is the same size and format, just in lower cells, throws out an error. Any ideas? "Bernie Deitrick" wrote: Richard, It is always a good idea to fully identify the ranges by sheet when operating between sheets. Re-write like so: With Worksheets("SheetName") .Range("A2:J5").Sort Key1:=.Range("J2"), Key2:=.Range("I2"), Key3:=.Range("G2"), _ Order1:=xlDescending, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With HTH, Bernie MS Excel MVP "RichardG" wrote in message ... I have the following macro to sort a range of cells :- Range("A2:J5").Select Range("J2").Activate Selection.Sort Key1:=Range("J2"), Key2:=Range("I2"), Key3:=Range("G2"), Order1:=xlDescending, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom That works fine when attahced to a button, bit I wanted it to work automatically when I update a second sheet that drives formulas on the original sheet. I'm using the "Private Sub worksheet_Calculate()" event to drive it, but using the same code as above gives me a Range selection error. Anyone any ideas how to get round this? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chabnging macros to functions
Richard,
Post your code - sorting shouldn't result in an error unless .... your key cells aren't in the sort range, etc...... Better to see your code. HTH, Bernie MS Excel MVP "RichardG" wrote in message ... Thanks for that, it works. Now a smaller secondary problem. I have a total of 8 groups to sort, all on the same sheet and all consecutive. The first group will sort, but the sort for the second grouping, which is the same size and format, just in lower cells, throws out an error. Any ideas? "Bernie Deitrick" wrote: Richard, It is always a good idea to fully identify the ranges by sheet when operating between sheets. Re-write like so: With Worksheets("SheetName") .Range("A2:J5").Sort Key1:=.Range("J2"), Key2:=.Range("I2"), Key3:=.Range("G2"), _ Order1:=xlDescending, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With HTH, Bernie MS Excel MVP "RichardG" wrote in message ... I have the following macro to sort a range of cells :- Range("A2:J5").Select Range("J2").Activate Selection.Sort Key1:=Range("J2"), Key2:=Range("I2"), Key3:=Range("G2"), Order1:=xlDescending, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom That works fine when attahced to a button, bit I wanted it to work automatically when I update a second sheet that drives formulas on the original sheet. I'm using the "Private Sub worksheet_Calculate()" event to drive it, but using the same code as above gives me a Range selection error. Anyone any ideas how to get round this? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chabnging macros to functions
My code is as follows :-
****************** Private Sub worksheet_Calculate() Application.ScreenUpdating = False With Worksheets("Table") .Range("A2:J5").Sort Key1:=.Range("J2"), Key2:=.Range("I2"), Key3:=.Range("G2"), _ Order1:=xlDescending, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom .Range("A6:J9").Sort Key1:=.Range("J6"), Key2:=.Range("I6"), Key3:=.Range("G6"), _ Order1:=xlDescending, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom .Range("A10:J16").Sort Key1:=.Range("J10"), Key2:=.Range("I10"), Key3:=.Range("G10"), _ Order1:=xlDescending, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom .Range("A17:J20").Sort Key1:=.Range("J17"), Key2:=.Range("I17"), Key3:=.Range("G17"), _ Order1:=xlDescending, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom .Range("A18:J21").Sort Key1:=.Range("J18"), Key2:=.Range("I18"), Key3:=.Range("G18"), _ Order1:=xlDescending, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom .Range("A22:J25").Sort Key1:=.Range("J22"), Key2:=.Range("I22"), Key3:=.Range("G22"), _ Order1:=xlDescending, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom .Range("A26:J29").Sort Key1:=.Range("J26"), Key2:=.Range("I26"), Key3:=.Range("G26"), _ Order1:=xlDescending, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom .Range("A30:J33").Sort Key1:=.Range("J30"), Key2:=.Range("I30"), Key3:=.Range("G30"), _ Order1:=xlDescending, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With Application.ScreenUpdating = True End Sub ******************** I simply took your code and adjusted the cell ranges for each set. One thing I've just thoguth of but not sure if it will afect the running of the macro, not all the cells are changed at the same time. "Bernie Deitrick" wrote: Richard, Post your code - sorting shouldn't result in an error unless .... your key cells aren't in the sort range, etc...... Better to see your code. HTH, Bernie MS Excel MVP "RichardG" wrote in message ... Thanks for that, it works. Now a smaller secondary problem. I have a total of 8 groups to sort, all on the same sheet and all consecutive. The first group will sort, but the sort for the second grouping, which is the same size and format, just in lower cells, throws out an error. Any ideas? "Bernie Deitrick" wrote: Richard, It is always a good idea to fully identify the ranges by sheet when operating between sheets. Re-write like so: With Worksheets("SheetName") .Range("A2:J5").Sort Key1:=.Range("J2"), Key2:=.Range("I2"), Key3:=.Range("G2"), _ Order1:=xlDescending, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With HTH, Bernie MS Excel MVP "RichardG" wrote in message ... I have the following macro to sort a range of cells :- Range("A2:J5").Select Range("J2").Activate Selection.Sort Key1:=Range("J2"), Key2:=Range("I2"), Key3:=Range("G2"), Order1:=xlDescending, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom That works fine when attahced to a button, bit I wanted it to work automatically when I update a second sheet that drives formulas on the original sheet. I'm using the "Private Sub worksheet_Calculate()" event to drive it, but using the same code as above gives me a Range selection error. Anyone any ideas how to get round this? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chabnging macros to functions
Richard,
Your code worked fine for me - no errors. The only thing that looks out of place is that you do ..Range("A17:J20").Sort then follow with an overlapping range: ..Range("A18:J21").Sort Also, the sorting may fire the calc event again, so try putting this at the top: Application.EnableEvents = False And this at the bottom: Application.EnableEvents = True HTH, Bernie MS Excel MVP "RichardG" wrote in message ... My code is as follows :- ****************** Private Sub worksheet_Calculate() Application.ScreenUpdating = False With Worksheets("Table") .Range("A2:J5").Sort Key1:=.Range("J2"), Key2:=.Range("I2"), Key3:=.Range("G2"), _ Order1:=xlDescending, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom .Range("A6:J9").Sort Key1:=.Range("J6"), Key2:=.Range("I6"), Key3:=.Range("G6"), _ Order1:=xlDescending, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom .Range("A10:J16").Sort Key1:=.Range("J10"), Key2:=.Range("I10"), Key3:=.Range("G10"), _ Order1:=xlDescending, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom .Range("A17:J20").Sort Key1:=.Range("J17"), Key2:=.Range("I17"), Key3:=.Range("G17"), _ Order1:=xlDescending, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom .Range("A18:J21").Sort Key1:=.Range("J18"), Key2:=.Range("I18"), Key3:=.Range("G18"), _ Order1:=xlDescending, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom .Range("A22:J25").Sort Key1:=.Range("J22"), Key2:=.Range("I22"), Key3:=.Range("G22"), _ Order1:=xlDescending, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom .Range("A26:J29").Sort Key1:=.Range("J26"), Key2:=.Range("I26"), Key3:=.Range("G26"), _ Order1:=xlDescending, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom .Range("A30:J33").Sort Key1:=.Range("J30"), Key2:=.Range("I30"), Key3:=.Range("G30"), _ Order1:=xlDescending, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With Application.ScreenUpdating = True End Sub ******************** I simply took your code and adjusted the cell ranges for each set. One thing I've just thoguth of but not sure if it will afect the running of the macro, not all the cells are changed at the same time. "Bernie Deitrick" wrote: Richard, Post your code - sorting shouldn't result in an error unless .... your key cells aren't in the sort range, etc...... Better to see your code. HTH, Bernie MS Excel MVP "RichardG" wrote in message ... Thanks for that, it works. Now a smaller secondary problem. I have a total of 8 groups to sort, all on the same sheet and all consecutive. The first group will sort, but the sort for the second grouping, which is the same size and format, just in lower cells, throws out an error. Any ideas? "Bernie Deitrick" wrote: Richard, It is always a good idea to fully identify the ranges by sheet when operating between sheets. Re-write like so: With Worksheets("SheetName") .Range("A2:J5").Sort Key1:=.Range("J2"), Key2:=.Range("I2"), Key3:=.Range("G2"), _ Order1:=xlDescending, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With HTH, Bernie MS Excel MVP "RichardG" wrote in message ... I have the following macro to sort a range of cells :- Range("A2:J5").Select Range("J2").Activate Selection.Sort Key1:=Range("J2"), Key2:=Range("I2"), Key3:=Range("G2"), Order1:=xlDescending, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom That works fine when attahced to a button, bit I wanted it to work automatically when I update a second sheet that drives formulas on the original sheet. I'm using the "Private Sub worksheet_Calculate()" event to drive it, but using the same code as above gives me a Range selection error. Anyone any ideas how to get round this? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I combine MACROS and functions? | New Users to Excel | |||
| macros with functions | Excel Worksheet Functions | |||
| excel functions in macros | Excel Programming | |||
| VBA, Macros, functions, or formulas | Excel Programming | |||
| Macros and IF Functions | Excel Programming | |||
Hybrid Mode
