ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Sorting Issue (https://www.excelbanter.com/excel-programming/375244-sorting-issue.html)

ew
Sorting Issue
 
I am trying to sort three seperate arrays using the following code:

'Overhead broken down by site
'Denver
OVHsite.Range("E4").Copy
DataWks.Range("J28").PasteSpecial Paste:=xlValues
'Coastal
OVHsite.Range("O4").Copy
DataWks.Range("K28").PasteSpecial Paste:=xlValues
'Orem
OVHsite.Range("J4").Copy
DataWks.Range("L28").PasteSpecial Paste:=xlValues
'Overhead broken down by exception
'Denver
OVHsite.Range("D7:D13").Copy
DataWks.Range("N4").PasteSpecial Paste:=xlValues
'Coastal
OVHsite.Range("N7:N13").Copy
DataWks.Range("P4").PasteSpecial Paste:=xlValues
'Orem
OVHsite.Range("I7:I13").Copy
DataWks.Range("R4").PasteSpecial Paste:=xlValues
'Sort Ovh
DataWks.Range("N4:O10").sort Key1:=Range("N4"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
DataWks.Range("P4:Q10").sort Key1:=Range("P4"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
DataWks.Range("R4:S10").sort Key1:=Range("R4"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

I am receiving the following error:

Run-time error '1004':
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.

What am I doing wrong? Any help would be appriciated....Thanks!

Jim Cone
Sorting Issue
 
See my answer to your same question of the 13th ...
"Sorting Question" from "ewagz"
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"EW"
wrote in message
I am trying to sort three seperate arrays using the following code:

'Overhead broken down by site
'Denver
OVHsite.Range("E4").Copy
DataWks.Range("J28").PasteSpecial Paste:=xlValues
'Coastal
OVHsite.Range("O4").Copy
DataWks.Range("K28").PasteSpecial Paste:=xlValues
'Orem
OVHsite.Range("J4").Copy
DataWks.Range("L28").PasteSpecial Paste:=xlValues
'Overhead broken down by exception
'Denver
OVHsite.Range("D7:D13").Copy
DataWks.Range("N4").PasteSpecial Paste:=xlValues
'Coastal
OVHsite.Range("N7:N13").Copy
DataWks.Range("P4").PasteSpecial Paste:=xlValues
'Orem
OVHsite.Range("I7:I13").Copy
DataWks.Range("R4").PasteSpecial Paste:=xlValues
'Sort Ovh
DataWks.Range("N4:O10").sort Key1:=Range("N4"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
DataWks.Range("P4:Q10").sort Key1:=Range("P4"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
DataWks.Range("R4:S10").sort Key1:=Range("R4"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

I am receiving the following error:

Run-time error '1004':
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.

What am I doing wrong? Any help would be appriciated....Thanks!

ew
Sorting Issue
 
No good Jim. I need to know how to select the worksheet or better yet, a
better explanation to a novice coder. Thanks.

"Jim Cone" wrote:

See my answer to your same question of the 13th ...
"Sorting Question" from "ewagz"
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"EW"
wrote in message
I am trying to sort three seperate arrays using the following code:

'Overhead broken down by site
'Denver
OVHsite.Range("E4").Copy
DataWks.Range("J28").PasteSpecial Paste:=xlValues
'Coastal
OVHsite.Range("O4").Copy
DataWks.Range("K28").PasteSpecial Paste:=xlValues
'Orem
OVHsite.Range("J4").Copy
DataWks.Range("L28").PasteSpecial Paste:=xlValues
'Overhead broken down by exception
'Denver
OVHsite.Range("D7:D13").Copy
DataWks.Range("N4").PasteSpecial Paste:=xlValues
'Coastal
OVHsite.Range("N7:N13").Copy
DataWks.Range("P4").PasteSpecial Paste:=xlValues
'Orem
OVHsite.Range("I7:I13").Copy
DataWks.Range("R4").PasteSpecial Paste:=xlValues
'Sort Ovh
DataWks.Range("N4:O10").sort Key1:=Range("N4"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
DataWks.Range("P4:Q10").sort Key1:=Range("P4"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
DataWks.Range("R4:S10").sort Key1:=Range("R4"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

I am receiving the following error:

Run-time error '1004':
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.

What am I doing wrong? Any help would be appriciated....Thanks!

Jim Cone
Sorting Issue
 
The answer I posted on the 13th was...
"My guess is that the DataWks sheet is not the active sheet.
The Key1 ranges all refer to the active sheet."

Which means you could select the sheet before running the sort code ...
DataWks.Select

- or even better -

prefix "range" with DataWks. in three places ...

Key1:=DataWks.Range("N4"),
Key1:=DataWks.Range("P4")
Key1:=DataWks.Range("R4")

Informative reading here ...
http://www.cpearson.com/excel/newposte.htm
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html





"EW"
wrote in message
No good Jim.
I need to know how to select the worksheet or better yet,
a better explanation to a novice coder. Thanks.

"Jim Cone" wrote:
See my answer to your same question of the 13th ...
"Sorting Question" from "ewagz"
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"EW"
wrote in message
I am trying to sort three seperate arrays using the following code:
'Overhead broken down by site
'Denver
OVHsite.Range("E4").Copy
DataWks.Range("J28").PasteSpecial Paste:=xlValues
'Coastal
OVHsite.Range("O4").Copy
DataWks.Range("K28").PasteSpecial Paste:=xlValues
'Orem
OVHsite.Range("J4").Copy
DataWks.Range("L28").PasteSpecial Paste:=xlValues
'Overhead broken down by exception
'Denver
OVHsite.Range("D7:D13").Copy
DataWks.Range("N4").PasteSpecial Paste:=xlValues
'Coastal
OVHsite.Range("N7:N13").Copy
DataWks.Range("P4").PasteSpecial Paste:=xlValues
'Orem
OVHsite.Range("I7:I13").Copy
DataWks.Range("R4").PasteSpecial Paste:=xlValues
'Sort Ovh
DataWks.Range("N4:O10").sort Key1:=Range("N4"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
DataWks.Range("P4:Q10").sort Key1:=Range("P4"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
DataWks.Range("R4:S10").sort Key1:=Range("R4"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

I am receiving the following error:
Run-time error '1004':
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.

What am I doing wrong? Any help would be appriciated....Thanks!


All times are GMT +1. The time now is 03:07 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com