使用匹配函数时出现Excel VBA运行时错误'13‘类型不匹配

Question

编写此代码的目的是执行以下操作：

从列表中获取唯一标识符(ASIN)，从单独的工作表中获取唯一关键字，并在第三个工作表中找到这两个标识符的交集

代码需要遍历每个ASIN的所有关键字。如果"if statement“为TRUE，则关键字将被添加到一个列表("result")中，该列表将通过循环进行连接。一旦内部循环完成，它将添加到一个单元格，然后移动到下一个ASIN，循环通过相同的关键字列表。

运行时，我收到一个运行时错误'13‘类型不匹配。然而，我不确定哪里有不匹配的地方。

ASIN是字符串；关键字是字符串。

如有任何帮助，我们不胜感激！

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

我能够创建一个解决方案，并简化一些代码。谢谢你的帮忙!

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

结束子对象

Answer 1

您正在将来自两个application.match语句的结果传递给变量。在继续之前，请确认您匹配了某项内容。

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，我不知道下面两行代码在外部循环中的用途。

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

你搞错了两个循环："For Each i In asin_rng“和"For Each j In keyword_rng”。

你应该有类似这样的东西：

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

致以问候。光盘