vba Excel 匹配单元格时返回整行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26911168/
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 Return Whole row when it Matches Cell
提问by Matt
I have a workbook with two sheets.
我有一本工作簿，里面有两张纸。
Sheet 1 has all of the data.
表 1 包含所有数据。
Sheet 2 is currently blank and I am planning to use a VLOOKUP to return any row that matches the certain cell.
工作表 2 当前为空白，我计划使用 VLOOKUP 返回与特定单元格匹配的任何行。
In sheet 1 in column E there are different values in each cell, I want to return any that say tyre
在 E 列的工作表 1 中，每个单元格中有不同的值，我想返回任何表示 tyre
I want them to copy the whole row of data whenever column E contains the word tyre. The word tyre is in Cell B1 in Sheet2
我希望他们在 E 列包含单词 tyre 时复制整行数据。轮胎一词位于 Sheet2 的单元格 B1 中
I have currently tried this code which is in sheet 2 but just getting a #VALUE! error.
我目前已经尝试了第 2 表中的这段代码，但只是得到了一个 #VALUE！错误。
=VLOOKUP($B,'sheet1'!E:E,0,FALSE)
回答by Matteo NNZ
I would rather use a VBA approach. Just run a macro which says:
我宁愿使用 VBA 方法。只需运行一个宏，它说：
Public Sub specialLookUp()
Dim keyword As String: keyword = Sheets("sheet2").Range("B1").Value
Dim countRows1 As Long, countRows2 As Long
countRows1 = 2 'the first row of your dataset in sheet1
endRows1 = 1000 'the last row of your dataset in sheet1
countRows2 = 2 'the first row where you want to start writing the found rows
For j = countRows1 To endRows1
If Sheets("sheet1").Range("E" & j).Value = keyword Then
Sheets("sheet2").Rows(countRows2).Value = Sheets("sheet1").Rows(j).Value
countRows2 = countRows2 + 1
End If
Next j
End Sub
Please note that now you can hardcode the beginning and the end of your dataset in sheet1, since you've told me your data have not an ID or any kind of field which is mandatory.
请注意，现在您可以在 sheet1 中硬编码数据集的开头和结尾，因为您告诉我您的数据没有 ID 或任何类型的必填字段。
