Type Mismatch??

[Otto Moehrbach]

Excel 2002 & WinXP
The following macro simply arranges (sorts) the sheets in the order of an
existing list.
The variable "c" is declared as Long.
Hovering the mouse pointer over the variables (after debug) results in:
SortList.Count = 25 'Correct
SortList(c) = "WYRI" 'Correct, last name in the list.
The error occurred in the first loop of the For loop.
The error is "Type Mismatch".
The offending line is:
Sheets(SortList(c)).Move Before:=Sheets(1)
For troubleshooting I put a message box before the For loop asking for
SortList.Address and SortList.Parent.Name. Both are correct.
What is happening to cause a "Type Mismatch" error?
Thanks for your help. Otto

Sub SortByAPAQ()
Dim SortList As Range
With Sheets("Utility")
If WhichSort = "AP3" Then
Set SortList = .Range("AP3", .Range("AP" &
Rows.Count).End(xlUp))
Else
Set SortList = .Range("AQ3", .Range("AQ" &
Rows.Count).End(xlUp))
End If
End With
For c = SortList.Count To 1 Step -1
Sheets(SortList(c)).Move Before:=Sheets(1)
Next
End Sub

 

[Tom Ogilvy]

change it to:

For c = SortList.Count To 1 Step -1
Sheets(SortList(c).Value).Move Before:=Sheets(1)
Next

 

[Otto Moehrbach]

Tom
Thanks for that. I've been bitten by that same mistake before. Memory
is the second thing to go. Otto

 

[Guest]

Perhaps try Sheets(SortList(c).Value). It might be seeing SortList(c) as a
Range object instead of the String value it contains.

 

[Otto Moehrbach]

You're right. That's exactly what was happening. Thanks. Otto