EXCEL VBA - 遍历列中的单元格，如果不为空，则将单元格值打印到另一列中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27000953/
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 VBA - Loop through cells in a column, if not empty, print cell value into another column
提问by HappyMango
I'm very new to Excel VBA and haven't quite familiarized myself with all the different functions and such, and I'm quite sure I understand how to use IF statements within FOR loops, so I'm not quite sure how to go about creating the code.
我对 Excel VBA 非常陌生，还不太熟悉所有不同的函数等，而且我很确定我了解如何在 FOR 循环中使用 IF 语句，所以我不太确定如何去做关于创建代码。
This is what I would like to have happen:
这是我希望发生的事情：
A1 Food B1 Selected? D1 Selections
A2 Grapes B2 Yes D2 Grapes
A3 Tomato B3 D3 Mango
A4 Mango B4 Yes D4 Spinach
A5 Spinach B5 Yes
A6 Carrots B6
A7 Onion B7
My thought process:
我的思考过程：
1) Create a FOR loop in range of B2 to B7 to check for the value 'YES'
2) If the value is yes, the corresponding value in the adjacent cell in A should be printed into D.
3) Else, it should continue looping through the cells.
1) 在 B2 到 B7 的范围内创建一个 FOR 循环来检查值 'YES'
2) 如果值为是，则应将 A 中相邻单元格中的相应值打印到 D 中。
3) 否则，应继续循环通过细胞。
I imagine that VLOOKUP is also involved somewhere within the if statement, and variables need to be defined to establish the range?
我想 VLOOKUP 也涉及 if 语句中的某个地方，需要定义变量来建立范围？
Would appreciate any help.
将不胜感激任何帮助。
Thanks!
谢谢！
回答by Gary's Student
Give this a try:
试试这个：
Sub FoodPicker()
Dim N As Long, i As Long, j As Long
N = Cells(Rows.Count, "A").End(xlUp).Row
j = 2
For i = 2 To N
If Cells(i, "B").Value = "Yes" Then
Cells(j, "C").Value = Cells(i, "A").Value
j = j + 1
End If
Next i
End Sub
and if you are willing to use 1,2,3instead of yes,yes,yesyou can avoid the macro.
如果您愿意使用1,2,3而不是yes,yes,yes，您可以避免使用宏。
回答by Sam
Assuming you have headers in row 1:
假设您在第 1 行有标题：
Sub SO()
Dim i As Long
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
If LCase(Cells(i, 2).Value) = "yes" Then Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Value = Cells(i, 1).Value
Next i
End Sub
