Error 13 Mismatch

[lalaluye]

Basically my code is trying to extract certain numeral parts of a text string on row 1 and sorting it from smallest to biggest.

I get an error on this line

If CInt(MinValue) >= CInt(MinTemp) Then

Hovering my mouse over MinValue and MinTemp where the error occurred, I get empty strings

Wondering if anyone can tell me the problem with my code? Can I not convert the string to integer?

Sub DataSort(SummarySheet As Worksheet, WorkBk As Workbook, Position As Integer, LastCol As Long, LastRow As Long)


    Dim MinValue As String
    Dim MinTemp As String
    Dim MinCol As Long
    Dim NCol As Integer
    Dim NRow As Integer
    Dim NCount As Long
    Dim Digits As Integer
    
    If Position = 13 Then
        Digits = 2 'aa
    ElseIf Position = 15 Then
        Digits = 2 'bb
    ElseIf Position = 17 Then
        Digits = 1 'c
    ElseIf Position = 18 Then
        Digits = 1 'd
    ElseIf Position = 19 Then
        Digits = 1 'e
    ElseIf Position = 20 Then
        Digits = 2 'ff
    ElseIf Position = 22 Then
        Digits = 1 'g
    ElseIf Position = 23 Then
        Digits = 2 'hh
    ElseIf Position = 25 Then
        Digits = 2 'ii
    Else
        Digits = 1 'j
    End If
    


    For NCol = 1 To LastCol
    
        MinValue = Mid("A1", Position, Digits)
        MinCol = 1
        
        NRow = 1
    
        For NCount = 2 To LastCol - NCol + 1
                
            MinTemp = Mid(Cells(1, NCount), Position, Digits)
                
            If CInt(MinValue) >= CInt(MinTemp) Then
                        
                MinValue = MinTemp
                MinCol = NCount
                
            End If
            
        Next NCount
        
        Set SourceRange = WorkBk.Worksheets(1).Range(ActiveSheet.Cells(1, MinCol), ActiveSheet.Cells(LastRow, MinCol))
        Set DestRange = SummarySheet.Cells(NRow, NCol)
        Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
                        SourceRange.Columns.Count)
        
        DestRange.Value = SourceRange.Value
        
        NRow = NRow + DestRange.Rows.Count
        
        Columns(MinCol).EntireColumn.Delete
        
    Next NCol


    
End Sub

 

[lalaluye]

I have also tried

MinTemp = Mid(WorkBk.Worksheets(1).Cells(1, NCount), Position, Digits)
MinValue = Mid(WorkBk.Worksheets(1).Cells(1, 1), Position, Digits)

Instead, but got an error 438

 

[ukmikeb]

Hi

Try :-

       MinValue = Mid([A1], Position, Digits)

The statement you originally had was looking at the string "A1" rather than the cell!

hth

 

[lalaluye]

Nvm noticed I had a typo for worksheets in my code!