Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Error 1004 with Sort in Excel 2010 VBA

Posted on 2013-05-30
3
Medium Priority
?
1,226 Views
Last Modified: 2013-05-31
I'm trying to do a 2 key sort in Excel 2010 and am having a problem.

I went to the worksheet, turned on macro record, selected the data I wanted to sort, clicked on the sort button, set my parameters and clicked OK.  It sorted fine. Then I stopped the macro recording.

Then, I went to the macros button, selected the macro I just recorded and clicked run.  The code worked fine up to the .Apply statement and then generated a 1004 error.  WHY???

This is the code the macro recording generated:
Sub fixsort2()
'
' fixsort2 Macro
'

'
    ActiveWorkbook.Worksheets("PistonTest").QueryTables(1).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("PistonTest").QueryTables(1).Sort.SortFields.Add Key _
        :=Range("E2:E6025"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("PistonTest").QueryTables(1).Sort.SortFields.Add Key _
        :=Range("GR2:GR6025"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("PistonTest").QueryTables(1).Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Open in new window

0
Comment
Question by:cashonly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 

Author Comment

by:cashonly
ID: 39208175
More info.  I went oldschool and wrote the macro like in Excel 2000 and ran it in Excel 2010 with no problem:

Sub fixsortoldschool()
   ThisWorkbook.Worksheets("PistonTest").Range("A1:GU6025").Select
    Selection.Sort Key1:=Range("E1"), Order1:=xlAscending, Key2:=Range("GR1") _
        , Order2:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom

End Sub

Open in new window


But I'd still like to know why it doesn't work with the Excel 2010 method of sorting.
0
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 2000 total points
ID: 39209900
the xl2010 version is sorting .QueryTables(1).

it is using that as the source of the table.  why it throws that error I dont know without testing on workbook.
0
 

Author Comment

by:cashonly
ID: 39210606
robberbaron,

That's interesting.

I just created a new WB and populated a 50 row x 5 column range with random numbers, made them static and added a header.  Then I did the same steps in recording a macro of me sorting them on 2 different columns, one ASC and the other DESC.

This time it DID NOT use Query Tables.  I use Query Tables in parts of my code and didn't even catch it that the macro was using those for a sort which I DID NOT want.

Your keen observation skills have answered my question.  Thank you!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question