你能在 VBA excel 中用宏找到特定值的行号吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1983592/
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
Can you find the row number for a particular value with macro in VBA excel?
提问by excel34
I'm trying to find a command which returns the index of the row for which a particular value occurs. In addition, the value cannot be empty.
我试图找到一个命令，该命令返回出现特定值的行的索引。此外，该值不能为空。
In total, there are 3 worksheets. Worksheet A has a column with all the values. Worksheet B has a column with values which appear in worksheet A and columns with more information for that value and I want to copy that information into worksheet C.
总共有 3 个工作表。工作表 A 有一列包含所有值。工作表 B 有一列包含出现在工作表 A 中的值和包含该值更多信息的列，我想将该信息复制到工作表 C 中。
Say worksheet A is (lines represent empty cells):
说工作表 A 是（线条代表空单元格）：
a
b
c
Worksheet B looks like this before I run the macro:
在我运行宏之前，工作表 B 看起来像这样：
a 12 32
c 34 45
b 23 21
Worksheet C looks like this before I run the macro:
在我运行宏之前，工作表 C 看起来像这样：
a
b
c
and like this after I run the macro:
在我运行宏之后就像这样：
a 12 32
b 23 21
c 34 45
The structure of the macro looks like this:
宏的结构如下所示：
- If the value in worksheet A is not empty, look for a row that corresponds to the value in worksheet "B".
- Copy the information in the row from worksheet B.
- Paste it into row i in worksheet "C".
- 如果工作表 A 中的值不为空，请查找与工作表“B”中的值对应的行。
- 复制工作表 B 行中的信息。
- 将其粘贴到工作表“C”中的第 i 行。
回答by Bob Phillips
Try this
尝试这个
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim RowNum As Long
With Worksheets("C")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow
If Not IsError(Application.Match(.Cells(i, "A"), Worksheets("A").Columns(1), 0)) Then
RowNum = Application.Match(.Cells(i, "A"), Worksheets("B").Columns(1), 0)
Worksheets("B").Cells(RowNum, "B").Resize(, 2).Copy .Cells(i, "B")
End If
Next i
End With
End Sub
回答by Jeff Storey
Have you looked at Excel's VLookup function? You can use that to find a value in a sheet and then get another value from that row. each of your cells in C could use a VLookup to get the correct value from B (or blank if it does not exist).
你看过Excel的VLookup函数吗？您可以使用它在工作表中查找值，然后从该行获取另一个值。C 中的每个单元格都可以使用 VLookup 从 B 中获取正确的值（如果不存在则为空白）。
回答by excel34
yea , I allready tried it with VLOOKUP , but I only managed to the find a value from an array not the index of the row.
是的，我已经用 VLOOKUP 试过了，但我只能从数组中找到一个值，而不是行的索引。
ie =VLOOKUP(1,A2:C10,2)
即 =VLOOKUP(1,A2:C10,2)
