Sort using VBA giving error.....please help

**welchs101** · 05-19-2011, 09:58 AM

I am doing this sort in VBA and i keep getting this error

"The sort reference is not valid. Make sure that its within the data you want to sort and the first sory by box isnt the same or blank.

Workbooks(Portfolio_Filename).Worksheets(Sheet_Snapshot_Inputfile). _
    Range("A1:" & alphacol(last_col1 + 4) & last_row1).Sort _
    Key1:=Range(alphacol(sort_col1) & "1"), Order1:=xlAscending, _
    Key2:=Range(alphacol(sort_col2) & "1"), Order2:=xlAscending, _
    Key3:=Range(alphacol(sort_col3) & "1"), Order3:=xlAscending, _
    Header:=xlYes

i am at a loss. My data table is A1:U1145.

sort_col1 = 1
sort_col2=20
sort_col3=21

please help!

**TMS** · 05-19-2011, 10:09 AM

Can we see the workbook with this code in it? With some sample data?

I'd want to step through and see what was in the variables.

Regards

**welchs101** · 05-19-2011, 10:47 AM

i wish i could post the data.........but i cant right now

I did do a macro record and then put this into the code and it worked.......take a look . any ideas on why this worked please please please please let me know!!!!!!!!!!!!!! I just cant figure it out.

Workbooks(Portfolio_Filename).Worksheets("Snapshot_Inputfile").Sort.SortFields.Clear
Workbooks(Portfolio_Filename).Worksheets("Snapshot_Inputfile").Sort.SortFields.Add Key:= _
    Range("A2:A1145"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
    :=xlSortNormal
Workbooks(Portfolio_Filename).Worksheets("Snapshot_Inputfile").Sort.SortFields.Add Key:= _
    Range("T2:T1145"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
    :=xlSortNormal
Workbooks(Portfolio_Filename).Worksheets("Snapshot_Inputfile").Sort.SortFields.Add Key:= _
    Range("U2:U1145"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
    :=xlSortNormal
With Workbooks(Portfolio_Filename).Worksheets("Snapshot_Inputfile").Sort
    .SetRange Range("A1:U1145")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

**welchs101** · 05-19-2011, 10:54 AM

here is the same code but with a few variable names

Workbooks(Portfolio_Filename).Worksheets("Snapshot_Inputfile").Sort.SortFields.Clear
Workbooks(Portfolio_Filename).Worksheets("Snapshot_Inputfile").Sort.SortFields.Add Key:= _
    Range(alphacol(sort_col1) & "2:" & alphacol(sort_col1) & "1145"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
    :=xlSortNormal
Workbooks(Portfolio_Filename).Worksheets("Snapshot_Inputfile").Sort.SortFields.Add Key:= _
    Range(alphacol(sort_col2) & "2:" & alphacol(sort_col2) & "1145"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
    :=xlSortNormal
Workbooks(Portfolio_Filename).Worksheets("Snapshot_Inputfile").Sort.SortFields.Add Key:= _
    Range(alphacol(sort_col3) & "2:" & alphacol(sort_col3) & "1145"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
    :=xlSortNormal
With Workbooks(Portfolio_Filename).Worksheets("Snapshot_Inputfile").Sort
    .SetRange Range(alphacol(sort_col1) & "2:" & alphacol(sort_col3) & "1145")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

**snb** · 05-19-2011, 11:04 AM

with Workbooks(Portfolio_Filename).Worksheets(Sheet_Snapshot_Inputfile)
  .cells(1).currentregion.Sort .[A1],,.[T1],,,.[U1],,xlYes
end with

PS. Please do not use 'Help'or 'Please' in the threadtitle. The readability of your post won't be improved by using an abundance of exclamationsigns either.

**romperstomper** · 05-19-2011, 10:58 AM

Original code should have been:

With Workbooks(Portfolio_Filename).Worksheets(Sheet_Snapshot_Inputfile)
    .Range("A1:" & alphacol(last_col1 + 4) & last_row1).Sort _
    Key1:=.Range(alphacol(sort_col1) & "1"), Order1:=xlAscending, _
    Key2:=.Range(alphacol(sort_col2) & "1"), Order2:=xlAscending, _
    Key3:=.Range(alphacol(sort_col3) & "1"), Order3:=xlAscending, _
    Header:=xlYes
End With

so that all Range calls are qualified with the worksheet.

**welchs101** · 05-19-2011, 11:09 AM

Romperstomper,

YOU ARE THE MAN!!!!!!!!!!

Thanks to all who tried to help much appreicated!!!!!!!!!!!!

It worked......i added the with and end-with (ie...i added the code you suggested) and it worked!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

THANKS!

**welchs101** · 05-19-2011, 11:14 AM

snb,

sorry i wont use such language again.......i was just really frustrated........but again sorry!

Also the code from snb works great as well!

THANKS GUYS!

**romperstomper** · 05-19-2011, 11:41 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save