vba - 简单的 Excel VBA 搜索和查找功能提供#VALUE!错误

Question

这是我 10 年来第二次使用 VBA/Excel，这并不是一个有趣的返回。我正在尝试做一些非常简单的事情，但我不确定我的数据类型是否错误或什么，调试器并没有给我太多继续。在我使用带有#VALUE 的自定义函数的单元格中执行失败!错误。这让我觉得我的数据类型无效，但我引用了几个不同的来源，找不到问题所在。

该函数应在描述单元格文本中搜索存储在 Lookup1 范围中的任何子字符串的任何匹配项，然后使用 lookup2 范围进行哈希表样式转换(如果找到)。

Function ExtractCategory(Description As String, Lookup1 As Range, _
Lookup2 As Range) As String

    Dim txt As String
    Dim SubjCell As Range

    For Each rRange In Lookup1
        SubjCell = rRange
        txt = SubjCell.Value

        If InStr(txt, Description) <> 0 Then
            ExtractCategory = Application.WorksheetFunction.Lookup(txt, _
            Lookup1, Lookup2)
            Exit For
        Else
            ExtractCategory = "Not Found"
        End If
    Next rRange
End Function

我仍然不确定的唯一类型问题是 txt = SubjCell.Value .当我尝试使用 SubjCell.Text , IDE 将其取消大写为 text ...不知道为什么。

Answer 1

试试这个(两个工作 - 尝试和测试 )

Function ExtractCategory(Description As String, Lookup1 As Range, _
Lookup2 As Range) As String
    Dim rRange As Range

    For Each rRange In Lookup1
        If InStr(1, rRange.Value, Description) Then
            ExtractCategory = Evaluate("=lookup(" & rRange.Value & "," & _
            Lookup1.Address & "," & Lookup2.Address & ")")
            Exit For
        Else
            ExtractCategory = "Not Found"
        End If
    Next rRange
End Function

这与

Function ExtractCategory(Description As String, Lookup1 As Range, _
Lookup2 As Range) As String
    Dim rRange As Range

    For Each rRange In Lookup1
        If InStr(1, rRange.Value, Description) Then
            ExtractCategory = Application.WorksheetFunction.Lookup(rRange.Value, _
            Lookup1, Lookup2)
            Exit For
        Else
            ExtractCategory = "Not Found"
        End If
    Next rRange
End Function

编辑

快照