Problem sorting data
 

Hello,

I'm using Excel 2003 and am having trouble sorting data. Here is the code
that I am using:

Application.Worksheets("Sheet1").Select
Application.Worksheets("Sheet1").Range("A1:A8").Se lect
Selection.Sort Key1:=Range("A1"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Here is my error message:

The sort reference is not valid. Make sure that it's within the data you
want to sort, and the first Sort By box isn't the same or blank.

The code comes alomst entirely from a the Macro I recorded, which works.
Meaning, if I select this range manually, the data sorts without a hiccup.
But if I try and execute code genned by the same sort Macro, VBA throws an
error. The only lines that did not come from the Macro are the first line,
selecting the worksheet and the "Application.Worksheets("Sheet1")." I
appended in front of Range.

Here is the data I have in Sheet1, Column A, Rows 1-8:

89100400
93092800
93701200
170074488
319529926
390162282
494995008
597663764

Totally confused.

Any help would be greatly appreciated.

Thanks,

Scott

Problem sorting data
 

this works for me

With Worksheets("Sheet1").Range("A1:A8")
.Sort Key1:=Range("A1"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With


--


Gary


"scottydel" wrote in message
...
Hello,

I'm using Excel 2003 and am having trouble sorting data. Here is the code
that I am using:

Application.Worksheets("Sheet1").Select
Application.Worksheets("Sheet1").Range("A1:A8").Se lect
Selection.Sort Key1:=Range("A1"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Here is my error message:

The sort reference is not valid. Make sure that it's within the data you
want to sort, and the first Sort By box isn't the same or blank.

The code comes alomst entirely from a the Macro I recorded, which works.
Meaning, if I select this range manually, the data sorts without a hiccup.
But if I try and execute code genned by the same sort Macro, VBA throws an
error. The only lines that did not come from the Macro are the first line,
selecting the worksheet and the "Application.Worksheets("Sheet1")." I
appended in front of Range.

Here is the data I have in Sheet1, Column A, Rows 1-8:

89100400
93092800
93701200
170074488
319529926
390162282
494995008
597663764

Totally confused.

Any help would be greatly appreciated.

Thanks,

Scott

Problem sorting data
 

Where is the code? Is it behind a worksheet or in a General module?

I'd use:

with activeworkbook.Worksheets("Sheet1").Range("A1:A8")
.cells.Sort Key1:=.Range("A1"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
end with

The dots mean that that thing (.cells and .range("a1")) belong to the object in
the previous with statement--in this case sheet1 of the activeworkbook.

scottydel wrote:

Hello,

I'm using Excel 2003 and am having trouble sorting data. Here is the code
that I am using:

Application.Worksheets("Sheet1").Select
Application.Worksheets("Sheet1").Range("A1:A8").Se lect
Selection.Sort Key1:=Range("A1"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Here is my error message:

The sort reference is not valid. Make sure that it's within the data you
want to sort, and the first Sort By box isn't the same or blank.

The code comes alomst entirely from a the Macro I recorded, which works.
Meaning, if I select this range manually, the data sorts without a hiccup.
But if I try and execute code genned by the same sort Macro, VBA throws an
error. The only lines that did not come from the Macro are the first line,
selecting the worksheet and the "Application.Worksheets("Sheet1")." I
appended in front of Range.

Here is the data I have in Sheet1, Column A, Rows 1-8:

89100400
93092800
93701200
170074488
319529926
390162282
494995008
597663764

Totally confused.

Any help would be greatly appreciated.

Thanks,

Scott

--

Dave Peterson

Problem sorting data
 

Dave,

The code was behind a worksheet, and I think this was my problem. I had the
code behind one sheet, but was trying to sort data on another sheet. Doesn't
seem that difficult with the ability to select sheets, not sure why it wasn't
working. Any insight there?

But the good news is, I moved the code to a generic module, and it is
working now.

-Scott

"Dave Peterson" wrote:

Where is the code? Is it behind a worksheet or in a General module?

I'd use:

with activeworkbook.Worksheets("Sheet1").Range("A1:A8")
.cells.Sort Key1:=.Range("A1"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
end with

The dots mean that that thing (.cells and .range("a1")) belong to the object in
the previous with statement--in this case sheet1 of the activeworkbook.

scottydel wrote:

Hello,

I'm using Excel 2003 and am having trouble sorting data. Here is the code
that I am using:

Application.Worksheets("Sheet1").Select
Application.Worksheets("Sheet1").Range("A1:A8").Se lect
Selection.Sort Key1:=Range("A1"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Here is my error message:

The sort reference is not valid. Make sure that it's within the data you
want to sort, and the first Sort By box isn't the same or blank.

The code comes alomst entirely from a the Macro I recorded, which works.
Meaning, if I select this range manually, the data sorts without a hiccup.
But if I try and execute code genned by the same sort Macro, VBA throws an
error. The only lines that did not come from the Macro are the first line,
selecting the worksheet and the "Application.Worksheets("Sheet1")." I
appended in front of Range.

Here is the data I have in Sheet1, Column A, Rows 1-8:

89100400
93092800
93701200
170074488
319529926
390162282
494995008
597663764

Totally confused.

Any help would be greatly appreciated.

Thanks,

Scott

--

Dave Peterson

Problem sorting data
 

When the code is in a general module, then range("a1") will refer to the
activesheet.

But when the code is behind a worksheet, then that unqualified range will refer
to the worksheet that owns the code--and that wasn't sheet1 of the
activeworkbook.

You could have specified the range:

Application.Worksheets("Sheet1").Range("A1:A8").Se lect
Selection.Sort Key1:=Application.Worksheets("Sheet1").Range("A1") ,

But I find it easier to understand if the .select's are dropped.

scottydel wrote:

Dave,

The code was behind a worksheet, and I think this was my problem. I had the
code behind one sheet, but was trying to sort data on another sheet. Doesn't
seem that difficult with the ability to select sheets, not sure why it wasn't
working. Any insight there?

But the good news is, I moved the code to a generic module, and it is
working now.

-Scott

"Dave Peterson" wrote:

Where is the code? Is it behind a worksheet or in a General module?

I'd use:

with activeworkbook.Worksheets("Sheet1").Range("A1:A8")
.cells.Sort Key1:=.Range("A1"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
end with

The dots mean that that thing (.cells and .range("a1")) belong to the object in
the previous with statement--in this case sheet1 of the activeworkbook.

scottydel wrote:

Hello,

I'm using Excel 2003 and am having trouble sorting data. Here is the code
that I am using:

Application.Worksheets("Sheet1").Select
Application.Worksheets("Sheet1").Range("A1:A8").Se lect
Selection.Sort Key1:=Range("A1"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Here is my error message:

The sort reference is not valid. Make sure that it's within the data you
want to sort, and the first Sort By box isn't the same or blank.

The code comes alomst entirely from a the Macro I recorded, which works.
Meaning, if I select this range manually, the data sorts without a hiccup.
But if I try and execute code genned by the same sort Macro, VBA throws an
error. The only lines that did not come from the Macro are the first line,
selecting the worksheet and the "Application.Worksheets("Sheet1")." I
appended in front of Range.

Here is the data I have in Sheet1, Column A, Rows 1-8:

89100400
93092800
93701200
170074488
319529926
390162282
494995008
597663764

Totally confused.

Any help would be greatly appreciated.

Thanks,

Scott

--

Dave Peterson


--

Dave Peterson

Problem sorting data
 

On Jul 26, 6:14 pm, Dave Peterson wrote:
When the code is in a general module, then range("a1") will refer to the
activesheet.

But when the code is behind a worksheet, then that unqualified range will refer
to the worksheet that owns the code--and that wasn't sheet1 of the
activeworkbook.

You could have specified the range:

Application.Worksheets("Sheet1").Range("A1:A8").Se lect
Selection.Sort Key1:=Application.Worksheets("Sheet1").Range("A1") ,

But I find it easier to understand if the .select's are dropped.





scottydel wrote:

Dave,

The code was behind a worksheet, and I think this was my problem. I had the
code behind one sheet, but was trying to sort data on another sheet. Doesn't
seem that difficult with the ability to select sheets, not sure why it wasn't
working. Any insight there?

But the good news is, I moved the code to a generic module, and it is
working now.

-Scott

"Dave Peterson" wrote:

Where is the code? Is it behind a worksheet or in a General module?

I'd use:

with activeworkbook.Worksheets("Sheet1").Range("A1:A8")
.cells.Sort Key1:=.Range("A1"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
end with

The dots mean that that thing (.cells and .range("a1")) belong to the object in
the previous with statement--in this case sheet1 of the activeworkbook.

scottydel wrote:

Hello,

I'm using Excel 2003 and am having trouble sorting data. Here is the code
that I am using:

Application.Worksheets("Sheet1").Select
Application.Worksheets("Sheet1").Range("A1:A8").Se lect
Selection.Sort Key1:=Range("A1"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Here is my error message:

The sort reference is not valid. Make sure that it's within the data you
want to sort, and the first Sort By box isn't the same or blank.

The code comes alomst entirely from a the Macro I recorded, which works.
Meaning, if I select this range manually, the data sorts without a hiccup.
But if I try and execute code genned by the same sort Macro, VBA throws an
error. The only lines that did not come from the Macro are the first line,
selecting the worksheet and the "Application.Worksheets("Sheet1")." I
appended in front of Range.

Here is the data I have in Sheet1, Column A, Rows 1-8:

89100400
93092800
93701200
170074488
319529926
390162282
494995008
597663764

Totally confused.

Any help would be greatly appreciated.

Thanks,

Scott

--

Dave Peterson

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Not sure if you need an automatic sort but this code automatically
sorts column 13 or col 16 exclusively whenever any new value is added
to the columns
These columns DO NOT have a header. It only sorts a single column at
a time and doesn't sort many columns by any single column.

'070625-DD Sort some list columns automatically in add value
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 13 Or Target.Column = 16 Then
Columns(Target.Column).Sort _
Key1:=Cells(1, Target.Column), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
'Target.CurrentRegion.Name = "NameList"
End If
End Sub

Problem sorting data
 

The code was sorting a range on a different worksheet--not the sheet that got
changed.


ssGuru wrote:
<<snipped

Not sure if you need an automatic sort but this code automatically
sorts column 13 or col 16 exclusively whenever any new value is added
to the columns
These columns DO NOT have a header. It only sorts a single column at
a time and doesn't sort many columns by any single column.

'070625-DD Sort some list columns automatically in add value
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 13 Or Target.Column = 16 Then
Columns(Target.Column).Sort _
Key1:=Cells(1, Target.Column), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
'Target.CurrentRegion.Name = "NameList"
End If
End Sub

--

Dave Peterson