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

What do you mean 'I am uncertain where the mismatch is however.' ? Go into the VBE's Tools, Options, General and set Error Trapping to Break on unhandled errors. — user4039065
I appreciate that help. I'm new to VBA and was having trouble figuring out how to effectively debug. Thanks! — Dys_Lexi_A

Camille Desmots Camille Desmots · Accepted Answer · 2018-03-27T21:51:04

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

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

2 Answers