CODEWITHSUNDEEP
excelvbasearchfind
mengmeng
mengmeng

Reputation: 1496

VBA Type mismatch in Find method

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

Upvotes: 1

Views: 943

Answers (1)

user4039065
user4039065

Reputation:

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

Upvotes: 1

Related Questions