Subscript Out of Range When Sorting

[reberryjr]

I don't know why I'm having issues with sorting, as I've done it a number of times in other projects. I've referred to my old code, as well as the web and I've tried the following snippets to no avail. I keep getting a "subscript out of range" error. Even when I copy and paste the macro recorder's snippet, I get an error.

'***Attempt 1
'ActiveWorkbook.Worksheets("DOADS").Sort Key:=Range("AH1:AH" & mPNLR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
'With ActiveWorkbook.Worksheets("DOADS").Sort
'    .SetRange Range("AH2:AH" & mPNLR)
'    .Header = xlNo
'    .MatchCase = False
'    .Orientation = xlTopToBottom
'    .SortMethod = xlPinYin
'    .Apply
'End With

'Attempt 2
'mP.Range("AH1:AH" & mPNLR).Sort Key1:=Range("AH1"), order1:=xlAscending, Header:=xlYes

'Attempt 3
'mP.Sort.SortFields.Clear

'mP.Sort.SortFields.Add Key:=mP.Range("AH1:AH" & mPNLR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal, Header:=xlYes

'Attempt 4
'mP.Range("AH1:AH" & mPNLR).Sort Key:=mP.Range("AH1:AH" & mPNLR), Order:=xlAscending, Header:=xlYes

'Attempt 5
'ActiveWorkbook.Worksheets("Names").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Names").Sort.SortFields.Add Key:=Range("AF1:AF" & mLiqLR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers

'With ActiveWorkbook.Worksheets("Names").Sort
'    .SetRange Range("AF2:AF" & mLiqLR)
'    .Header = xlNo
'    .MatchCase = False
'    .Orientation = xlTopToBottom
'    .SortMethod = xlPinYin
'    .Apply
'End With

 

[Joe4]

It looks like you have some last row variable named "mLiqLR", but I do not see it being calculated/set anywhere in your code.
So its value would be 0, which would cause problems, as Excel has no row 0.

 

[reberryjr]

It looks like you have some last row variable named "mLiqLR", but I do not see it being calculated/set anywhere in your code.
So its value would be 0, which would cause problems, as Excel has no row 0.

It's there, I just didn't include it in the code.

mLiqLR = mP.Range("AF" & Rows.Count).End(xlUp).Row

I tried using mPNLR but thought it may have been the issue since the last row in column A is over 1K, and AF only has about 15 record, but it still won't work.

 

[Joe4]

It's there, I just didn't include it in the code.

mLiqLR = mP.Range("AF" & Rows.Count).End(xlUp).Row

I tried using mPNLR but thought it may have been the issue since the last row in column A is over 1K, and AF only has about 15 record, but it still won't work.

In order for us to try to analyze your code, it is important to include all those details.
Otherwise, it is hard for us to tell what you may be doing wrong. It is also very helpful to see a sample of the data that you are trying to run this on.

What is the value of "mLiqLR" when the code is being run?
You can put this line of code just before your Sort command to see what it returns:

MsgBox mLiqLR

Also, when you get the error message, if you hit the "Debug" option, which line of code does it highlight?

 

[reberryjr]

In order for us to try to analyze your code, it is important to include all those details.
Otherwise, it is hard for us to tell what you may be doing wrong. It is also very helpful to see a sample of the data that you are trying to run this on.

What is the value of "mLiqLR" when the code is being run?
You can put this line of code just before your Sort command to see what it returns:

MsgBox mLiqLR

It comes back with 15, which is accurate.

 

[Joe4]

OK, can you show us a sample of the data it is running on?

Also, when you get the error message, if you hit the "Debug" option, which line of code does it highlight?

 

[reberryjr]

OK, can you show us a sample of the data it is running on?

Also, when you get the error message, if you hit the "Debug" option, which line of code does it highlight?

The data is literally just a Header and 14 names under the header. Right now, this line is being highlighted during the debugging

ActiveWorkbook.Worksheets("Names").Sort.SortFields.Add Key:=Range("AF1:AF" & mLiqLR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers

 

[Joe4]

Do you actually have a sheet named "Names" in your workbook?

 

[reberryjr]

Do you actually have a sheet named "Names" in your workbook?

I do

 

[Joe4]

It seems to work just fine for me.

Do you have any merged or protected cells in the workbook
Or perhaps any errors in your data?