Excel：如何将包含某些文本的行复制到另一个工作表 (VBA)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45209392/
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: How to copy a row if it contains certain text to another worksheet (VBA)
提问by Hufflegigans
I'm looking to use a marco that would be able to search a column in said sheet and if certain text is found - in my case the word "FAIL" - copy that entire rows data/formatting and paste it into another sheet - sheet 4 in my case - along with any other rows that contained that specific text.
我希望使用能够在所述工作表中搜索一列的 marco，如果找到某些文本 - 在我的情况下是“失败”这个词 - 复制整行数据/格式并将其粘贴到另一个工作表中 - 工作表4 在我的情况下 - 以及包含该特定文本的任何其他行。
i have been using this code but it only copy pastes one row then stops rather than going through and copying any rows with "FAIL"
我一直在使用此代码，但它只复制粘贴一行然后停止而不是通过并复制任何带有“FAIL”的行
Sub Test()
For Each Cell In Sheets(1).Range("H:H")
If Cell.Value = "FAIL" Then
matchRow = Cell.Row
Rows(matchRow & ":" & matchRow).Select
Rows(matchRow & ":" & matchRow).Select
Selection.Copy
Sheets(4).Select
ActiveSheet.Rows(matchRow).Select
ActiveSheet.Paste
Sheets(4).Select
End If
Next
End Sub
First post and brand new to VBA so apologies if too vague.
第一次发帖并且是 VBA 的新手，如果太含糊，请见谅。
采纳答案by Shai Rado
Try the code below (explanation inside the code as comments):
试试下面的代码（代码中的解释作为注释）：
Option Explicit
Sub Test()
Dim Cell As Range
With Sheets(1)
' loop column H untill last cell with value (not entire column)
For Each Cell In .Range("H1:H" & .Cells(.Rows.Count, "H").End(xlUp).Row)
If Cell.Value = "FAIL" Then
' Copy>>Paste in 1-line (no need to use Select)
.Rows(Cell.Row).Copy Destination:=Sheets(4).Rows(Cell.Row)
End If
Next Cell
End With
End Sub
回答by Vityata
Try like this:
像这样尝试：
Option Explicit
Sub TestMe()
Dim Cell As Range
Dim matchRow As Long
With Worksheets(1)
For Each Cell In .Range("H:H")
If Cell.Value = "FAIL" Then
matchRow = .Cell.Row
.Rows(matchRow & ":" & matchRow).Select
.Rows(matchRow & ":" & matchRow).Select
Selection.Copy
Worksheets(4).Select
Worksheets(4).Rows(matchRow).Select
Worksheets(4).Paste
.Select
End If
Next
End With
End Sub
The problem in your code is that you do not reference the worksheets all the time correctly. Thus it does not work correctly.
您代码中的问题是您没有始终正确地引用工作表。因此它不能正常工作。
As a 2. step, you can try to avoid all the selections in your code, it is a best practice to avoid using either Select or Activate in Excel VBA.
作为 2. 步骤，您可以尝试避免代码中的所有选择，最佳做法是避免在 Excel VBA 中使用 Select 或 Activate。
