Run time error 13 Type mismatch

Question

Run time error 13 Type mismatch

Navigation

#1 by (0 votes)

1

I have a problem with a macro that I am trying to make. You should look for a range of values that I give you in all the excel sheets and you should return the name of the sheets in which each value of the range appears. the error occurs when compiling returning

Run-time error '13' Type mismatch

Sub findRecurrence()
    Dim ws As Worksheet
    Dim strSearch As Range
    Dim rngSearch As Range
    Dim rngFound As String
    Dim i As Integer
    Dim xAddress As String

    xAddress = Application.ActiveWindow.RangeSelection.Address
    Set strSearch = Application.InputBox("Lookup values :", "Kutools for Excel", xAddress, Type:=8)
    For Each ws In Worksheets
        Set rngSearch = ws.Cells.Find(What:=strSearch)
        If Not rngSearch Is Nothing Then
            i = i + 1
            If i = 1 Then
                rngFound = rngSearch.Worksheet.Name
            Else
                rngFound = rngFound & ", " & rngSearch.Worksheet.Name
            End If
        End If
    Next ws

    MsgBox "'" & strSearch & "' found on the following worksheet(s): " & rngFound & "."
    End Sub

macros vb excel-2010

asked by jorgeshady 02.01.2018 в 11:27

source

1 answer

Read .txt separated by commas and save in a List or pointer to char in C Problem importing complex matrix Mathematica, ToExpression :: esntx: Could not parse

score 0 · Answer 1

The error probably occurs when you select a range greater than one cell. What happens is the following:

You are directly accessing the object strSearch which is a Range and not a String , by default the objects, when accessing these and not some of their properties, usually have a chain type representation, but It is a bad practice to use them in this way because we do not have security that will always have a value. In your example, when the chosen range is from a single cell there is no problem, the value is the text contained in it, the problem occurs when the range applies to several cells. The solution is to access the property strSearch.Text , this property, if the object is valid, it will always return a string, the value of the cell or blank ( "" ) if the range is more than one cell.

With what I mentioned earlier, we should only have a logic to make the message clearer when the range is greater than one cell. For example, I suggest the following:

MsgBox "'" & IIf(strSearch.Text <> "", strSearch.Text, "Content of range: " & strSearch.Address) & "' found on the following worksheet(s): " & rngFound & "."

As you can see, by IIf() we verify if strSearch.Text <> "" in which case the range is of a single cell and we can use strSearch.Text to show the searched content, otherwise, as the range is higher and we can not will show all the content, at least we inform the range by strSearch.Address