Sort problem

[Karen53]

Hi,

I keep getting a subscript out of range and can't find why. All of my
values are correct. Have I formatted something incorrectly?

Sub SortIDData(ShName, StartCol, EndCol, StartRow, EndRow)

Debug.Print "Starting SortIDData"

Debug.Print "wbkNew.Name " & wbkNew.Name
Debug.Print "ShName " & ShName & _
vbLf & "StartCol " & StartCol & _
vbLf & "StartRow " & StartRow & _
vbLf & "EndCol " & EndCol & _
vbLf & "Endrow " & EndRow

wbkNew.Sheets(ShName).Range(StartCol & StartRow & ":" & _
EndCol &
EndRow).Sort _
Key1:=Sheets(ShName).Range(StartCol & StartRow), _
Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=4, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub

 

[Lars-Åke Aspelin]

Hi,

I keep getting a subscript out of range and can't find why. All of my
values are correct. Have I formatted something incorrectly?

Sub SortIDData(ShName, StartCol, EndCol, StartRow, EndRow)

Debug.Print "Starting SortIDData"

Debug.Print "wbkNew.Name " & wbkNew.Name
Debug.Print "ShName " & ShName & _
vbLf & "StartCol " & StartCol & _
vbLf & "StartRow " & StartRow & _
vbLf & "EndCol " & EndCol & _
vbLf & "Endrow " & EndRow

wbkNew.Sheets(ShName).Range(StartCol & StartRow & ":" & _
EndCol &
EndRow).Sort _
Key1:=Sheets(ShName).Range(StartCol & StartRow), _
Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=4, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub

How does your Sub get to know about wbkNew ??

Lars-Åke

 

[Karen53]

Hi,

wbkNew is a global variable already set in another procedure. This value is
correct according to the debug.print statements.

 

[Dave Peterson]

Make sure you qualify all your ranges in that sort statement:

wbkNew.Sheets(ShName).Range(StartCol & StartRow & ":" & EndCol & EndRow).Sort _
Key1:=wbkNew.Sheets(ShName).Range(StartCol & StartRow), _
Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=4, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Or rewrite it to make it easier to see????

with wbkNew.Sheets(ShName).Range(StartCol & StartRow & ":" & EndCol & EndRow)
.cells.sort _
Key1:=.columns(1), _
Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=4, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End with

 

[Lars-Åke Aspelin]

Hi,

wbkNew is a global variable already set in another procedure. This value is
correct according to the debug.print statements.

Maybe you have a leading or trailing space in the ShName, not seen in
you Debug printout, that makes the wkbNew(ShName) give a "subscript
out of range".

Lars-Åke

 

[Lars-Åke Aspelin]

Maybe you have a leading or trailing space in the ShName, not seen in
you Debug printout, that makes the wkbNew(ShName) give a "subscript
out of range".

Lars-Åke

Try changing Sheets to Worksheets i.e. change

wbkNew.Sheets(ShName)

to

wbkNew.Worksheets(ShName)

Lars-Åke

 

[Gary Keramidas]

try this and see if it makes any difference. there wasn't a lot of information
to work with.

i hard coded the variables to show you how i did it. you can remove them. i
wasn't sure what you were using for start and end columns, numbers or letters,
so i used letters. if you use numbers, use cells instead of range.

Sub SortIDData()
Dim shname As String
Dim wbknew As Workbook
Dim startcol As String, endcol As String
Dim startrow As Long, endrow As Long
Debug.Print "Starting SortIDData"
Set wbknew = ActiveWorkbook
shname = "Sheet1"
startcol = "B"
startrow = 1
endcol = "G"
endrow = 3

'Debug.Print "wbkNew.Name " & wbkNew.Name
' Debug.Print "ShName " & shname & _
vbLf & "StartCol " & startcol & _
vbLf & "StartRow " & startrow & _
vbLf & "EndCol " & endcol & _
vbLf & "Endrow " & endrow

With wbknew.Sheets(shname).Range(startcol & startrow & ":" & endcol &
endrow)
.Sort Key1:=.Range(startcol & startrow), _
Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=4, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Sub