Type Mismatch??
 

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 Befo=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 Befo=Sheets(1)
Next
End Sub

Type Mismatch??
 

change it to:

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


--
Regards,
Tom Ogilvy


"Otto Moehrbach" wrote in message
...
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 Befo=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 Befo=Sheets(1)
Next
End Sub

Type Mismatch??
 

Tom
Thanks for that. I've been bitten by that same mistake before. Memory
is the second thing to go. Otto
"Tom Ogilvy" wrote in message
...
change it to:

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


--
Regards,
Tom Ogilvy


"Otto Moehrbach" wrote in message
...
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 Befo=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 Befo=Sheets(1)
Next
End Sub

Type Mismatch??
 

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


"Otto Moehrbach" wrote:

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 Befo=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 Befo=Sheets(1)
Next
End Sub

Type Mismatch??
 

You're right. That's exactly what was happening. Thanks. Otto
"K Dales" wrote in message
...
Perhaps try Sheets(SortList(c).Value). It might be seeing SortList(c) as
a
Range object instead of the String value it contains.
--
- K Dales


"Otto Moehrbach" wrote:

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 Befo=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 Befo=Sheets(1)
Next
End Sub