vba Excel VBA函数在单元格范围内查找值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22535449/
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
Excel VBA function to lookup value in a Cell range
提问by Joop
I know how to do this in excel using match and index. My spreadsheet has a lot of these lookups though and it has to be easily auditable.
我知道如何使用匹配和索引在 excel 中执行此操作。虽然我的电子表格有很多这样的查找，但它必须易于审核。
In pure excel formulas it is a simple:
在纯excel公式中，它很简单：
=index(match()...match()) of the table
although easy to do it created big headackes if the size of the table changes,etc. the readability of the formula is also bad. Named ranges for the table and the headers and columns make it even more difficult to troubleshoot.
虽然很容易做到，但如果表的大小发生变化等，它会造成很大的麻烦。公式的可读性也很差。表、标题和列的命名范围使得故障排除更加困难。
Col1 Col2 Col3
Row1 1 2 3
Row2 4 5 6
Row3 7 8 9
I am naming this range with the row and column names as first row and column respectively.
我用行名和列名分别命名这个范围作为第一行和第一列。
so the Excel variable will be called test_table.
所以 Excel 变量将被称为 test_table。
want to write a VBA function that i can call with:
想编写一个我可以调用的 VBA 函数：
=VBAlookup("Row2", "Col2", test_table)
returns 5
at moment i am using python with DataNitro and pandas to easily do this splicing. Not good at VBA so writing this in VBA going to take quite a while. Sure that I am not the first or only to look for this functionality, but google search does not seem to get me anywhere.
目前我正在使用带有 DataNitro 和 Pandas 的 python 来轻松地进行这种拼接。不擅长 VBA，所以用 VBA 写这个需要很长时间。当然，我不是第一个或唯一一个寻找此功能的人，但谷歌搜索似乎并没有让我找到任何地方。
Don't want to answer my own question, but my solution so far (adapted from @John Bustos' answer below) is:
不想回答我自己的问题，但到目前为止我的解决方案（改编自 @John Bustos 下面的回答）是：
Public Function VBAlookup(RowName As Variant, ColName As Variant, Table As Range) As Variant
Dim RowNum As Integer
Dim ColNum As Integer
VBAlookup = "Not Found"
For RowNum = 1 To Table.Rows.Count
If Table.Cells(RowNum, 1) = RowName Then
For ColNum = 1 To Table.Columns.Count
If Table.Cells(1, ColNum) = ColName Then
VBAlookup = Table.Cells(RowNum, ColNum)
End If
Next ColNum
End If
Next RowNum
End Function
This gets any type of content in correct format and also gives some feedback if value is not in the table
这会以正确的格式获取任何类型的内容，如果值不在表中，也会提供一些反馈
采纳答案by John Bustos
This should do it:
这应该这样做：
Public Function VBAlookup(RowName As String, ColName As String, Table As Range) As String
Dim RowNum As Integer
Dim ColNum As Integer
VBAlookup = ""
For RowNum = 1 To Table.Rows.Count
If Table.Cells(RowNum, 1).Text = RowName Then
For ColNum = 1 To Table.Columns.Count
If Table.Cells(1, ColNum).Text = ColName Then
VBAlookup = Table.Cells(RowNum, ColNum).Text
End If
Next ColNum
End If
Next RowNum
End Function
Note that I did not put in any error checking, etc, which would be good practice!
请注意，我没有进行任何错误检查等，这将是一个很好的做法！
