VBA Select Case type incompatibility

Question

I have got this sub and this function you see below. It gives me a runtime error 13: type incompatibility on the following row and I dont know whats wrong

Select Case ((Cell.Offset(0, -2).Value - Cell.Offset(0, -1).Value) - (CellX.Offset(0, -2).Value - CellX.Offset(0, -1).Value))

I am working on this piece of code for hours, so you are my last resort.

You may also need to know, that in the Cells of Range rngX there are cells with floating point numbers and cells with no content and in Cell.Offset(0,-2) and Cell.Offset(0,-1) there are integral numbers or cells with no content

Sub X()

    Dim boolFunction as Boolean

    With ThisWorkbook.Sheets("IntTab")
        boolFunction = Function1(.Range("O2:O19"))
        boolFunction = Function1(.Range("AA2:AA19"))
    End With

End Sub


Function Function1(ByVal rngX As Range) As Boolean

    Dim intTab As Integer

    For Each Cell In rngX
        intTab = 1
        For Each CellX In rngX
            Select Case Cell.Value - CellX.Value
                Case Is > 0
                    'Do nothing
                Case Is = 0
                    Select Case ((Cell.Offset(0, -2).Value - Cell.Offset(0, -1).Value) - (CellX.Offset(0, -2).Value - CellX.Offset(0, -1).Value))
                        Case Is > 0
                            'Do nothing
                        Case Is = 0
                            'Do nothing
                        Case Is < 0
                            intTab = intTab + 1
                    End Select
                Case Is < 0
                    intTab = intTab + 1
            End Select
        Next CellX
        Cell.Offset(0, (-9)).Value = intTab
    Next Cell

    Function1 = True

End Function

score 0 · Answer 1 · answered Mar 21 '18 at 21:21

I suspect that those "cells with no content" may have some spaces content

so just convert them to zero via the Val() function

Select Case ((val(cell.Offset(0, -2).Value) - val(cell.Offset(0, -1).Value)) - (val(cellX.Offset(0, -2).Value) - val(cellX.Offset(0, -1).Value)))

VBA Select Case type incompatibility

1 Answers1