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?

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?

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?

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?

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?

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?