使用 VBA 在 Excel 中搜索单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10437858/
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
Search for a Cell in Excel using VBA
提问by NCC
Problem: I would like to find a value of a cell next to or below the cell content a text value for a workbook.
问题：我想在单元格内容旁边或下方找到一个单元格的值作为工作簿的文本值。
Example: In Sheet2, I have two cells stand random (assume its index is unknown and total is not a defined name)
示例：在 Sheet2 中，我有两个随机的单元格（假设它的索引未知并且总数不是一个定义的名称）
I want to search for the value "200" stands next to the total and put it in sheet 2 (an active cell). In case, there are multiple cell that contains the word "Total" list all of them and if possible, put the name of the sheet that contains the cell that I am looking for. Value 200 Sheet2
我想在总数旁边搜索值“200”并将其放入工作表 2（活动单元格）。如果有多个包含“总计”一词的单元格，请列出所有这些单元格，如果可能，请输入包含我要查找的单元格的工作表的名称。值 200 Sheet2
My Approach: 1. User input
我的方法： 1. 用户输入
Go to each cell and search for it. This will take time if search for the whole limitation of cell in excel. So the search only limit to 100 columns x 10000 rows.
After find its index, offset to 1 columns to get the value
Write the result ActiveCell.Value = Search_Value. Then continue to search for the rest of sheets. Offset 1 coloum and 1 row to write the second value...
转到每个单元格并搜索它。如果在 excel 中搜索单元格的整个限制，这将需要时间。因此搜索仅限制为 100 列 x 10000 行。
找到它的索引后，偏移到 1 列以获取值
写入结果 ActiveCell.Value = Search_Value。然后继续搜索其余的工作表。偏移 1 列和 1 行以写入第二个值...
Searching is a very difficult concept, and I truly have no idea how to do the search part. Please help
搜索是一个非常困难的概念，我真的不知道如何做搜索部分。请帮忙
回答by SeanC
With Worksheets(1).Range("a1:a500")
counter=0
Set c = .Find("Total", lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
counter=counter+1
Worksheets(2).range("A1").offset(counter,0)=c.offset(0,1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
firstaddressholds the location of the first cell found (so we know when to stop); firstaddress.offset(0,1)will give you the value you are trying to save, so setting worksheet(2).range("a1").offset(counter,0)will list all the values it finds on the 2nd tab, from a1 down to however many it finds in the range
firstaddress保存找到的第一个单元格的位置（因此我们知道何时停止）；firstaddress.offset(0,1)会给你你想要保存的值，所以设置worksheet(2).range("a1").offset(counter,0)将列出它在第二个选项卡上找到的所有值，从 a1 到它在范围内找到的数量
