Excel VBA Run-Time Error '13' Type Mismatch When Working with a Match Function

Question

This code was written in order to do the following:

Take unique identifier from a list (ASIN) and a unique keyword from a separate worksheet and find the intersection of those two identifiers on a third worksheet

The code needs to loop through all keywords for each ASIN. If the "if statement" is TRUE, the keyword will be added to a list ("result") which will concatenate through the loop. Once the inner loop is finished, it will add to a cell and then move on to the next ASIN, looping through the same keyword list.

When running, I am getting a run-time error '13' Type Mismatch. I am uncertain where the mismatch is however.

ASINs are Strings; keywords are Strings.

Any help is appreciated!

Sub Search_Terms()
    Dim asin_rng As Range
    Dim keyword_rng As Range
    Dim contentcolnum As Variant
    Dim contentrownum As Variant
    Dim keywordrownum As Variant
    Dim productrownum As Variant
    Dim sheetName As String
    Dim result As String

    Set asin_rng = Worksheets("Background Search Term Analysis").Range("B2:B253")
    Set keyword_rng = Worksheets("Keyword Categorization").Range("A3:A159")

    For Each i In asin_rng
    contentrownum = Application.Match(i, Worksheets("Current Content Analysis").Range("B1:B256"), 0)
    productrownum = Application.Match(i, Worksheets("Product Categorization").Range("A1:A159"), 0)
    result = ""
        For Each j In keyword_rng
        contentcolnum = Application.Match(j, Worksheets("Current Content Analysis").Range("A2:FL2"), 0)
        keywordrownum = Application.Match(j, Worksheets("Keyword Categorization").Range("A1:A159"), 0)
            'if this product doesn't currently have the keyword in it then
            If Worksheets("Current Content Analysis").Cells(contentrownum, contentcolnum) = "FALSE" Then
                'if the keyword and product tagging matches add it to result
                If Worksheets("Keyword Categorization").Cells(keywordrownum, 2) = Worksheets("Product Categorization").Cells(productrownum, 3) And Worksheets("Keyword Categorization").Cells(keywordrownum, 3) = Worksheets("Product Categorization").Cells(productrownum, 4) And Worksheets("Keyword Categorization").Cells(keywordrownum, 4) = Worksheets("Product Categorization").Cells(productrownum, 5) Then
                result = result & "," & Worksheets("Keyword Categorization").Cells(keywordrownum, 1)
                End If
            End If
            Next j
        'once i go through all of my keywords, set ASIN background search term cell value equal to result
        Worksheets("Background Search Term analysis").Cells(productrownum, 4).Value = result
     Next i

End Sub

I was able to create a solution, plus simplify the code some. Thanks for your help!

Sub Search_Terms()
Dim asin_rng As Range
Dim keyword_rng As Range
Dim contentcolnum As Variant
Dim contentrownum As Variant
Dim keywordrownum As Variant
Dim productrownum As Variant
Dim sheetName As String
Dim result As String

'Set asin_rng = Worksheets("Background Search Term Analysis").Range("B5:B255").Cells
'Set keyword_rng = Worksheets("Keyword Categorization").Range("A4:A159").Cells

For Each i In Worksheets("Background Search Term Analysis").Range("B5:B255").Cells
    contentrownum = Application.Match(i, Worksheets("Current Content Analysis").Range("B1:B256").Cells, 0)
    productrownum = Application.Match(i, Worksheets("Product Categorization").Range("A1:A255").Cells, 0)

    For Each j In Worksheets("Keyword Categorization").Range("A4:A159").Cells
        contentcolnum = Application.Match(j, Worksheets("Current Content Analysis").Range("A2:FL2").Cells, 0)
        keywordrownum = Application.Match(j, Worksheets("Keyword Categorization").Range("A1:A159").Cells, 0)

       'if this product doesn't currently have the keyword in it then
        If Worksheets("Current Content Analysis").Cells(contentrownum, contentcolnum) = False And Worksheets("Keyword Categorization").Cells(keywordrownum, 2) = Worksheets("Product Categorization").Cells(productrownum, 3) And Worksheets("Keyword Categorization").Cells(keywordrownum, 3) = Worksheets("Product Categorization").Cells(productrownum, 4) And Worksheets("Keyword Categorization").Cells(keywordrownum, 4) = Worksheets("Product Categorization").Cells(productrownum, 5) Then
            result = result & "," & Worksheets("Keyword Categorization").Cells(keywordrownum, 1)
        End If
    Next j

   'once i go through all of my keywords, set ASIN background search term cell value equal to result
   Worksheets("Background Search Term Analysis").Cells(contentrownum, 4).Value = result
   result = ""
Next i

End Sub

Answer 1

You're passing the results from two application.match statements into variants. Confirm that you matched something before continuing.

For Each j In keyword_rng
    contentcolnum = Application.Match(j, Worksheets("Current Content Analysis").Range("A2:FL2"), 0)
    keywordrownum = Application.Match(j, Worksheets("Keyword Categorization").Range("A1:A159"), 0)
    if not iserror(contentcolnum) and not iserror(keywordrownum) then
        'if this product doesn't currently have the keyword in it then
        If not Worksheets("Current Content Analysis").Cells(contentrownum, contentcolnum) Then
            'if the keyword and product tagging matches add it to result
            If Worksheets("Keyword Categorization").Cells(keywordrownum, 2) = Worksheets("Product Categorization").Cells(productrownum, 3) And Worksheets("Keyword Categorization").Cells(keywordrownum, 3) = Worksheets("Product Categorization").Cells(productrownum, 4) And Worksheets("Keyword Categorization").Cells(keywordrownum, 4) = Worksheets("Product Categorization").Cells(productrownum, 5) Then
                result = result & "," & Worksheets("Keyword Categorization").Cells(keywordrownum, 1)
            End If
        End If
   End If
Next j

tbh, I have no idea what the following two lines are intended to accomplish in the outer loop.

contentrownum = Application.Match(i, Worksheets("Current Content Analysis").Range("B1:B256"), 0)
productrownum = Application.Match(i, Worksheets("Product Categorization").Range("A1:A159"), 0)

Answer 2

You are wrong with the 2 loops : "For Each i In asin_rng" and "For Each j In keyword_rng"

You should have something like :

For Each currentCell In Worksheets("Background").Range("B2:B253").Cells
   'Here you implement your code using currentCell.Value
Next currentCell

Regards. CD