Type mismatch in code searching within a cell

Question

I'm getting a type mismatch while searching through a cell for a period "." or "p". It worked when I only used the "." but after adding "p" I get a type mismatch. Is a Variant/Empty for my_txt only able to use integers and "."s? I'm trying to have my filter use .'s or p's to determine the outline level.

Sub ProcessDocV5()
    Dim Level As Range
    Dim i, j, q(1 To 50) As Long
    Dim numofchar As Long
    Dim filepath As String
    Dim filename As String
    Dim LastRow As Long
    Dim rowcallout As Long
    Dim columncallout As Long


    'scanf(Input the correct row and column numbers).
    rowcallout = InputBox("LOCATION ROW OF HEADERS?")
    columncallout = InputBox("LOCATION COLUMN OUTLINE? (A=1, B=2, ect...)")
     Debug.Print "rowcallout value is "; [rowcallout]
     Debug.Print "columncallout value is "; [columncallout]
    'END OF SCAN


    'ADJUST EXCEL SCREEN
    'stop screen updating
    Application.ScreenUpdating = False
    'show gridlines
    ActiveWindow.DisplayGridlines = True
    'remove borders
    ActiveWindow.DisplayGridlines = True
    Cells.Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone


    'group according to level column (Cell(row,column))
    Set Level = Range(Cells(rowcallout, columncallout), Cells(873, 2))
    Debug.Print "The value of Levels is "; Level.Address

    For i = rowcallout To Level.count

        Cells(i, columncallout).Select

        a = Len(Cells(i, columncallout))
        Debug.Print "A value is "; [a]
        my_txt = Replace(Cells(i, columncallout), "." Or "p", "", 1, -1, vbTextCompare)
        b = Len(my_txt)
        Debug.Print "B value is "; [b]
        numb_occur = a - b + 1
        Debug.Print [numb_occur]

        If numb_occur < 8 Then
            Rows(i).OutlineLevel = numb_occur - 1
        Else
            Rows(i).OutlineLevel = 8
        End If

    Next i


    With ActiveSheet.Outline
        .AutomaticStyles = False
        .SummaryRow = xlAbove
        .SummaryColumn = xlRight
    End With

    'Close tabs for neatness
    ActiveSheet.Outline.ShowLevels RowLevels:=8
    ActiveSheet.Outline.ShowLevels RowLevels:=7
    ActiveSheet.Outline.ShowLevels RowLevels:=6
    ActiveSheet.Outline.ShowLevels RowLevels:=5
    ActiveSheet.Outline.ShowLevels RowLevels:=4
    ActiveSheet.Outline.ShowLevels RowLevels:=3
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    ActiveSheet.Outline.ShowLevels RowLevels:=1


    End Sub

score 3 · Accepted Answer · edited May 23 '17 at 12:25

That's a lot of irrelevant code, but I managed to dig this one up:

my_txt = Replace(Cells(i, columncallout), "." Or "p", "", 1, -1, vbTextCompare)

"." Or "p" is an expression that VBA can't evaluate. The following is all we needed to see:

Debug.Print "." Or "p"

This instruction reproduces the exact problem you're having: a type mismatch error.

Or is a logical binary operator that, when used as such, evaluates to True or False. When used as a bitwise operator it can evaluate to a Long, but while VBA does a lot of implicit type conversions for us, there's a limit to it, and "." and "p" can't be converted to Long or Boolean values, so VBA throws this type mismatch error, saying "I don't know what to do with this".

Run both replacements sucessively instead:

my_txt = Replace(Cells(i, columncallout), ".", "", 1, -1, vbTextCompare)
my_txt = Replace(my_txt, "p", "", 1, -1, vbTextCompare)

Unrelated, but must read:

Type mismatch in code searching within a cell

1 Answers1