简单的 Excel VBA 搜索和查找功能提供 #VALUE！错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10857664/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Simple Excel VBA search and lookup function gives #VALUE! error
提问by neuroelectronic
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.
该函数应在描述单元格文本中搜索存储在 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 将其取消大写为text......不知道为什么。
采纳答案by Siddharth Rout
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
快照
