VBA Type mismatch in Find method

Question

I'm trying to create a function that will input "Yes" in the 11th column in every row if there's a word "Market" in a row. But i get the error mismatch.

Option Explicit

Sub findMarketing()
    Dim r As Range

    For Each rw In Worksheets("Sheet1").Rows
    Set r = rw.Range("A:G")

        If r.Find(What:="market") Then
            rw.Columns(11).Value = "Yes"
        Else
              rw.Columns(11).Value = "No"
        End If
    Next rw
End Sub

You get the error because the Range.Find method returns a range object. In the way you have constructed it, the default value of a range object (the Value property) will be returned. But your code is looking for a Boolean, thus the type mismatch. And there are other syntax errors in your code. I'm surprised not caught by Option Explicit. — Ron Rosenfeld
@RonRosenfeld I edited the Column to Columns. Can you help me have to construct it to a boolean? If "market" is found in a range, the 11th column will have the "Yes" value — mengmeng

Unknown Unknown · Accepted Answer · 2017-05-07T18:55:37

You have an answer from the comments (thanks Ron Rosenfeld) but you also do not want to go through 1,048,576 rows. Use the Intersect function to operate on columns A:G on any particular row and limit the rows to the worksheet's .UsedRange so you are only examining the rows that actually have data in them.

I've changed your method of locating 'Market' in columns A:G because you are not specifying enough parameters. The .Find function relies a lot on what was used last. If the user used Find or Replace on the worksheet and changed the Match Case, Match entire contents, etc then that is what you are going to be operating under. I'll assume that you want a non-case-sensitive, full cell value match. [edit] Apparently a wildcard partial match is required.

Option Explicit

Sub findMarketing()
    Dim r As Range, rw As Range

    With Worksheets("Sheet1")
        For Each rw In .UsedRange.Rows
            Set r = Intersect(rw, .Range("A:G"))

            If IsError(Application.Match("*market*", r.Cells, 0)) Then
                .Cells(rw.Row, "K") = "No"
            Else
                .Cells(rw.Row, "K") = "Yes"
            End If
        Next rw
    End With
End Sub

VBA Type mismatch in Find method

1 Answers