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

--
Thanks for your help.
Karen53

On Mon, 14 Apr 2008 12:26:02 -0700, Karen53
wrote:

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

Hi,

wbkNew is a global variable already set in another procedure. This value is
correct according to the debug.print statements.
--
Thanks for your help.
Karen53


"Lars-Ã…ke Aspelin" wrote:

On Mon, 14 Apr 2008 12:26:02 -0700, Karen53
wrote:

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

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





Karen53 wrote:

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

--
Thanks for your help.
Karen53

--

Dave Peterson

On Mon, 14 Apr 2008 13:04:01 -0700, Karen53
wrote:

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

On Mon, 14 Apr 2008 20:18:19 GMT, Lars-Åke Aspelin
wrote:

On Mon, 14 Apr 2008 13:04:01 -0700, Karen53
wrote:

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

Try changing Sheets to Worksheets i.e. change

wbkNew.Sheets(ShName)

to

wbkNew.Worksheets(ShName)

Lars-Åke

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

--


Gary


"Karen53" wrote in message
...
Hi,

wbkNew is a global variable already set in another procedure. This value is
correct according to the debug.print statements.
--
Thanks for your help.
Karen53


"Lars-Åke Aspelin" wrote:

On Mon, 14 Apr 2008 12:26:02 -0700, Karen53
wrote:

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