如果单元格的内容与条件 VBA-EXCEL 匹配，则将整行复制到另一个工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10669145/
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
Copy an entire row to another worksheet if a cell has content matches the condition VBA-EXCEL
提问by NCC
I would like to copy the Name and Description If Quantity on and is equal to zero so I can order the fruit that I do not have. It starts from A2 whenever it see "STOP" then it stops.
我想复制名称和描述，如果数量为 并且等于零，以便我可以订购我没有的水果。每当它看到“停止”时，它就会从 A2 开始，然后它就会停止。
I have the code:
我有代码：
However, the code does not stop by itself, I have to break it when it runs. It copies all empty rows between and entire row when I only need to have first 2 columns. My questions are 1. how to let the program knows it should stop when see "STOP" at column A? Only copy none empty row? and only copy first two column (or the number of columns that I would like to have)?
但是，代码不会自行停止，我必须在运行时将其中断。当我只需要前两列时，它会复制整行之间的所有空行。我的问题是 1. 如何让程序知道在 A 列看到“停止”时应该停止？只复制空行？并且只复制前两列（或我想要的列数）？
回答by chris neilsen
Stop the loop
The problem is in yourWhileclause: you are comparing the length of a string to a string - that will never beTRUECopy only non-empty rows
Test for valid dataCopy required number of columns
See sample code
停止循环
问题出在您的While子句中：您将字符串的长度与字符串进行比较-永远不会TRUE只复制非空行
测试有效数据复制所需的列数
查看示例代码
FWIW your code can do with a bit of improving - heres my take on your requirements
FWIW 你的代码可以做一些改进 - 这是我对你的要求的看法
Sub CopySample()
Dim shSrc As Worksheet
Dim shDst As Worksheet
Dim rSrc As Range
Dim rDst As Range
Dim numCol As Long ' number of columns to copy
On Error GoTo EH
numCol = 2
' select source and dest sheets
Set shSrc = ActiveWorkbook.Worksheets("Sheet1")
Set shDst = ActiveWorkbook.Worksheets("Sheet2")
' Select initial rows
Set rSrc = shSrc.Cells(2, 1)
Set rDst = shDst.Cells(23, 1)
' loop over source
Do While rSrc <> "STOP"
' Test Source row, Qty = 0 and Name is not blank
With rSrc
If .Offset(0, 2) = 0 And .Value <> "" Then
'Copy
.Resize(1, numCol).Copy rDst.Resize(1, numCol)
Set rDst = rDst.Offset(1, 0)
End If
End With
Set rSrc = rSrc.Offset(1, 0)
Loop
Exit Sub
EH:
MsgBox "Error " & Err.Description
End Sub
Note that looping over a range can be slow for large amounts of data. Probably OK in this case, but there are ways to improve speed.
请注意，对于大量数据，在一个范围内循环可能会很慢。在这种情况下可能没问题，但有一些方法可以提高速度。
