Excel 2010 VBA 如何在多个工作表中查找值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24020836/
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 2010 VBA How to lookup a value in multiple sheets?
提问by Mastermind
I have an excel file with 14 worksheets, i need to lookup for a value in different columns of each sheet, and if its found, return the name of the sheet where the value was found in a list on a summary sheet.
我有一个包含 14 个工作表的 excel 文件，我需要在每个工作表的不同列中查找一个值，如果找到，则返回在汇总表的列表中找到该值的工作表的名称。
I have tried with if and vlookup function but i can′t find a proper result, also i tried with pivot table but since i have multiple sheets its not working for me.
我已经尝试过 if 和 vlookup 功能，但我找不到合适的结果，我也尝试过使用数据透视表，但由于我有多个工作表，它对我不起作用。
I am hoping that somebody helps me with a bit of VBA coding to solve this issue.
我希望有人通过一些 VBA 编码来帮助我解决这个问题。
I am very newbie at coding and VBA and still i don′t understand all of it, so i haven′t tried to code myself, sorry for that.
我是编码和 VBA 的新手，但我仍然不了解所有内容，所以我没有尝试自己编码，抱歉。
回答by sbanders
Try using the following formula functions to create a solution. If you can't come up with something that works, post what you have and we can work through it.
尝试使用以下公式函数来创建解决方案。如果您无法提出有效的方法，请发布您所拥有的内容，我们可以解决它。
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
地址(row_num, column_num, [abs_num], [a1], [sheet_text])
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
IF(logical_test,value_if_true,value_if_false)
如果（logical_test，value_if_true，value_if_false）
Also consider using Named Ranges
还可以考虑使用命名范围
Let me know what you come up with :)
让我知道你想出了什么:)
回答by sharerware
Hope this helps. :)
希望这可以帮助。:)
Sub findStuff()
Dim ws As Worksheet
Dim strWhat As String
Dim rngSearch As Range
Dim rngFound As String
Dim i As Integer
strWhat = "Raspberry"
For Each ws In Worksheets
Set rngSearch = ws.Cells.Find(What:=strWhat)
If Not rngSearch Is Nothing Then
i = i + 1
If i = 1 Then
rngFound = rngSearch.Worksheet.Name
Else
rngFound = rngFound & ", " & rngSearch.Worksheet.Name
End If
End If
Next ws
MsgBox "'" & strWhat & "' found on the following worksheet(s): " & rngFound & "."
End Sub
