Run-time error '13': Type mismatch when sorting... 3

[snowcold]

CSV_Files()

Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean


    'Just setting up for the open file portion
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    'Change the folder to the location where the CSV files are stored
    
    csvfoldername = "C:\files"
    Set CSVfolder = fso.GetFolder(csvfoldername)

    For Each file In CSVfolder.Files
        If Right(file.Name, 4) = ".csv" Then
            Sheets.Add
            
            'Names the sheet after the file - restricts length to maximum 31 characters
            'allowed when naming an excel sheet
            
            ActiveSheet.Name = Left(file.Name, 31)
            
            
            With ActiveSheet.QueryTables.Add(Connection:= _
                "TEXT;" & file, Destination:=Range("A1"))
                .Name = Right(file.Name, (Len(file.Name) - Len(csvfoldername)))
                
                .PreserveFormatting = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .TextFilePlatform = xlWindows
                .TextFileStartRow = 1
                .TextFileParseType = xlDelimited
                .TextFileTextQualifier = xlTextQualifierDoubleQuote
                .TextFileCommaDelimiter = True
                .Refresh BackgroundQuery:=False
            End With
        End If
                        
    Next
    
    'get rid of the default sheets we did not use
    
    Application.DisplayAlerts = False
    
    For Each Worksheet In Worksheets
    
        If Left(Worksheet.Name, 5) = "Sheet" Then
        
            Sheets(Worksheet.Name).Delete
            
        End If
        
    Next
    
       
    SortDescending = False
    
    If ActiveWindow.SelectedSheets.Count = 1 Then
        FirstWSToSort = 1
        LastWSToSort = Worksheets.Count
    Else
        With ActiveWindow.SelectedSheets
            For N = 2 To .Count
                If .Item(N - 1).Index <> .Item(N).Index - 1 Then
                    MsgBox "You cannot sort non-adjacent sheets"
                    Exit Sub
                End If
            Next N
            FirstWSToSort = .Item(1).Index
            LastWSToSort = .Item(.Count).Index
        End With
    End If

    For M = FirstWSToSort To LastWSToSort
        For N = M To LastWSToSort
             If SortDescending = True Then
                   If Int(Mid(Worksheets(N).Name, 6)) > _
                      Int(Mid(Worksheet(M).Name, 6)) Then
                   Worksheets(N).Move Before:=Worksheets(M)
                End If
            Else
                If Int(Mid(Worksheets(N).Name, 6) < _ 
                   Int(Mid(Worksheet(M).Name, 6) Then
                    Worksheet(N).Move Before:=Worksheet(M)
                End If
            End If
        Next N
    Next M

    Application.DisplayAlerts = True

    'tidy up
    Set fso = Nothing

End Sub

As I am stepping throught this, the error is occuring here:

If Int(Mid(Worksheets(N).Name, 6) < Int(Mid(Worksheet(M).Name, 6)

any ideas why?

 

[xlbo]

off the top of my head - maybe the 6th character in the file name cannot be converted to an integer.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Bong]

the return from Mid(<string>,...) is a string. The argument of Int is a number.

_________________
Bob Rashkin

 

[PHV]

You may consider the Val function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

Actually no - VBA is very clever at interpretation. If the result of the Mid formula is a numeric string, the INT function will still work - it is only if the string cannot be interpreted as a number that it will fail

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[snowcold]

doesn't this take the string and converts it to int?

Int(Mid(Worksheets(N).Name, 6) < _
Int(Mid(Worksheet(M).Name, 6)

 

[snowcold]

actually, I looked into the Val.

I think I have other issues in my code, the following code snipet returns a value of 0...

lNumber = Val(Worksheets(N).Name)
lNumber = Val(Worksheets(M).Name)

The worksheets are titled:

RUN1 TEST1
...
RUN27 TEST3

so this function should not return a 0

 

[xlbo]

snowcold - what I am saying is that

Int(Mid(Worksheets(N).Name, 6)

will fail if the character in the 6th position in the file name is NOT a number. In BOTH your examples, the 6th character is a string and NOT a number and therfore cannot be converted to an integer

Mid(Worksheets(N).Name, 6) where Worksheets(N).Name = "RUN1 TEST1" will return "T"
Mid(Worksheets(M).Name, 6) where Worksheets(N).Name = "RUN27 TEST3" will return " "

Neither of these can possibly be converted to an integer so the code fails as the data types do not match (hence the type mismatch error)


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

Perhaps this ?
lNumber = Val(Replace(Worksheets(N).Name, "RUN", ""))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Bong]

Maybe you should just find the word "TEST" and take whatever is after that:
Right(Worksheets(N).Name,Len(Worksheets(N).Name)-InStr(1,Worksheets(N).Name,"TEST")-3) (or however many ")"s I need.

_________________
Bob Rashkin

 

[snowcold]

Thanks for all the replies...
I'm playing around with the different solutions, I'll provide feedback shortly.

Bong, actually I need to determine what is after RUN, then TEST...

Thanks again, check back for the feedback.

 

[Bong]

You might want to look into split
If Worksheets(N).Name returns, say, Run3 Test5, then a=split(Worksheets(N).Name, " ") will produce an array, a where a(0)=Run3 and a(1)=Test5.

_________________
Bob Rashkin

 

[snowcold]

Here is what I have done to sort the worksheets.
This statement cleared up the run-time error.

Worksheets.Item(N).Name

The following allows me to check if the TEST'X' value is less than the other TEST'X' value.
The names of the worksheets could be RUN1 TEST1, RUN1 TEST2, RUN2 TEST1, RUN2 TEST2...etc

LNumberN = Int(Mid(Worksheets.Item(N).Name, 4, 2))
LNumberM = Int(Mid(Worksheets.Item(M).Name, 4, 2))
If LNumberN = LNumberM Then
   LPositionN2 = InStr(Worksheets.Item(N).Name, ".")
   LPositionM2 = InStr(Worksheets.Item(M).Name, ".")   
   If Int(Mid(Worksheets.Item(N).Name, (LPositionN2 - 1), 1)) < Int(Mid(Worksheets.Item(M).Name, (LPositionM2 - 1), 1)) Then
   Worksheets(N).Move Before:=Worksheets(M)
End If

 

[snowcold]

Thanks to all for posting and helping me work throught the problem....