简单的Excel VBA搜索和查找功能提供了#VALUE！错误

This is the second time I've been using VBA/Excel in 10 years and it has not been a fun return. I'm trying to do something very simple, but I'm not sure if my datatypes are wrong or what, the debugger is not giving me much to go on. The execution fails in the cell where I use my custom function with the #VALUE! error. This makes me think my datatypes are invalid but I've referenced several different sources and cannot find what the issue is.

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

The function should search the Description cell text for any match of any substring stored in the Lookup1 range, then use the lookup2 range to do a hash-table style translation if it's found.

该函数应在Description单元格文本中搜索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

The only type issue I'm still unsure about is the txt = SubjCell.Value. When I try to use SubjCell.Text, the IDE de-capitalizes it to text... Not sure why.

我仍然不确定的唯一类型问题是txt = SubjCell.Value。当我尝试使用SubjCell.Text时，IDE将其去大写为文本...不确定原因。

1 个解决方案

#1

Try this (Both work - TRIED AND TESTED)

试试这个（两个工作 - 经过试验和测试）

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

Which is the same as

哪个是一样的

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

EDIT

编辑

SNAPSHOT

快照