Run-time error '13' - Type Mismatch doing a Find

Question

I've written the below code for some ActiveX textboxes I have on a worksheet that pull in data from another sheet and are controlled with a couple of buttons (previous and next).

All works good except for I keep getting the following error:

Run-time error '13':

Type mismatch

It's strange because the cells only contain text and are formatted as text, so I'm not sure why I keep getting the type mismatch.

The lines that cause the errors are:

Set sectionfound = .Find(what:=sectiontextbox.Object.Text, LookIn:=xlValues, lookat:=xlWhole)

Set titlefound = .Find(what:=titletextbox.Object.Text, LookIn:=xlValues, lookat:=xlWhole)

Set controlfound = .Find(what:=controltextbox.Object.Text, LookIn:=xlValues, lookat:=xlWhole)

Set guidancefound = .Find(what:=guidancetextbox.Object.Text, LookIn:=xlValues, lookat:=xlWhole)

The whole sub:

Sub UpdateTextBox(shift As Long)

Dim sectionfound As Range
Dim titlefound As Range
Dim controlfound As Range
Dim guidancefound As Range
Dim titlerange As Range
Dim sectionrange As Range
Dim controlrange As Range
Dim guidancerange As Range
Dim commentsrange As Range
Dim titletextbox As OLEObject
Dim sectiontextbox As OLEObject
Dim controltextbox As OLEObject
Dim guidancetextbox As OLEObject
Dim commentstextbox As OLEObject
Dim index As Long

With Worksheets("Tool")
    Set sectiontextbox = .OLEObjects("Section_Textbox")
    Set sectionrange = Worksheets("Annex_A").Range("B3:B165")
    Set titletextbox = .OLEObjects("Title_Textbox")
    Set titlerange = Worksheets("Annex_A").Range("C3:C165")
    Set controltextbox = .OLEObjects("Control_Textbox")
    Set controlrange = Worksheets("Annex_A").Range("D3:D165")
    Set guidancetextbox = .OLEObjects("Guidance_Textbox")
    Set guidancerange = Worksheets("Annex_A").Range("E3:E165")
    Set commentstextbox = .OLEObjects("Comments_Textbox")
    Set commentsrange = Worksheets("Annex_A").Range("F3:F165")
End With

With sectionrange
    If sectiontextbox.Object.Text <> "" Then
        Set sectionfound = .Find(what:=sectiontextbox.Object.Text, LookIn:=xlValues, lookat:=xlWhole)
        If Not sectionfound Is Nothing Then index = sectionfound.Row - .Rows(1).Row + 1
    End If

    index = index + shift
    Select Case index
        Case Is > .Rows.Count
            index = .Rows.Count
        Case Is < 1
            index = 1
    End Select

    sectiontextbox.Object.Text = .Rows(index)
End With

With titlerange
    If titletextbox.Object.Text <> "" Then
        Set titlefound = .Find(what:=titletextbox.Object.Text, LookIn:=xlValues, lookat:=xlWhole)
        If Not titlefound Is Nothing Then index = titlefound.Row - .Rows(1).Row + 1
    End If

    index = index + shift
    Select Case index
        Case Is > .Rows.Count
            index = .Rows.Count
        Case Is < 1
            index = 1
    End Select

    titletextbox.Object.Text = .Rows(index)
End With

With controlrange
    If controltextbox.Object.Text <> "" Then
        Set controlfound = .Find(what:=controltextbox.Object.Text, LookIn:=xlValues, lookat:=xlWhole)
        If Not controlfound Is Nothing Then index = controlfound.Row - .Rows(1).Row + 1
    End If

    index = index + shift
    Select Case index
        Case Is > .Rows.Count
            index = .Rows.Count
        Case Is < 1
            index = 1
    End Select

    controltextbox.Object.Text = .Rows(index)
End With

With guidancerange
    If guidancetextbox.Object.Text <> "" Then
        Set guidancefound = .Find(what:=guidancetextbox.Object.Text, LookIn:=xlValues, lookat:=xlWhole)
        If Not guidancefound Is Nothing Then index = guidancefound.Row - .Rows(1).Row + 1
    End If

    index = index + shift
    Select Case index
        Case Is > .Rows.Count
            index = .Rows.Count
        Case Is < 1
            index = 1
    End Select

    guidancetextbox.Object.Text = .Rows(index)
End With

End Sub


Private Sub Next_Button_Click()
UpdateTextBox -1
End Sub

Private Sub Previous_Button_Click()
UpdateTextBox 1
End Sub

You should not be getting that particular error on these particular lines, and I cannot reproduce it. — GSerg
Would you put the line msgbox sectiontextbox.Object.Text before the first error line, run it, and report back the results? — n8.
@n8. The errors are at different points. So the first one that errors is the guidancetextbox. When putting the messagebox in, it shows the data correctly, until it gets to the first cell that breaks it (E6). I've even tried just putting 'test' into E6 incase it was the data in the cell breaking it. But it still errors out on that one first. Does that make sense? — user1269522

n8. n8. · Accepted Answer · 2018-02-06T20:43:24

It seems to me that you should have this code only once in the sub:

index = index + shift
Select Case index
    Case Is > 165 '.Rows.Count
        index = 165 '.Rows.Count
    Case Is < 1
        index = 1
End Select

Without seeing the form or the data it doesn't make sense that you would iterate the row multiple times, it seems like the row is supposed to move only one per the shift parameter.

I would put this after your With Worksheets("Tool") block and before your With sectionrange block

My theory is that these multiple iterations are causing the sub to attempt to look beyond the declared range, a row that is greater than 165.

EDIT

EDIT EDIT

I added a series of message boxes to let us understand what happens, step by step.

Try this, let me know what happens?

With guidancerange
        msgbox("guidancetextbox.Object.Text row: """ & guidancetextbox.Object.Text & """") 

    If guidancetextbox.Object.Text <> "" Then
        Index = .Find(what:=guidancetextbox.Object.Text, LookIn:=xlValues, lookat:=xlWhole).Row
        msgbox("Index row: """ & Index & """") 
        msgbox(".Rows(1).Row + 1: """ & .Rows(1).Row + 1 & """") 
    End If

    Index = Index + shift
    msgbox(".Rows.Count: """ & .Rows.Count & """") 
    Select Case Index + shift
        Case Is > .Rows.Count
            Index = .Rows.Count
        Case Is < 1
            Index = 1
    End Select

    msgbox("Index row: """ & Index & """") 
    guidancetextbox.Object.Text = .Rows(Index)
End With

Run-time error '13' - Type Mismatch doing a Find

1 Answers