EXCEL VBA - 循环通过列中的单元格（如果不为空），将单元格值打印到另一列中

问题描述

我非常熟悉Excel VBA，并不熟悉所有不同的功能，所以我相信我明白如何在FOR循环中使用IF语句，所以我不太确定如何创建代码。

这是我想要发生的事情：

  A1食品B1选择？ D1选择
 A2葡萄B2是D2葡萄
 A3番茄B3 D3芒果
 A4芒果B4是D4菠菜
 A5菠菜B5是
 A6胡萝卜B6 
 A7洋葱B7 
  

我的想法过程：

1）在B2到B7的范围内创建一个FOR循环，以检查值YES

2）如果该值为是，则应打印A中相邻单元格中的相应值D.

3）否则，它应该继续循环通过单元格。

我想象，VLOOKUP也涉及到if语句和变量需要定义以确定范围？

感谢任何帮助。

谢谢！

解决方案

尝试一下：

  Sub FoodPicker（）
 Dim N As Long，i As Long，j As Long 
 N = Cells（Rows.Count，A）。End（xlUp）.Row 
j = 2 
对于i = 2到N 
如果单元格（i，B）。值=是然后
单元格（j，C）。值=单元格（i，A）。价值
j = j + 1 
结束If 
 Next i 
 End Sub 
  

，如果您愿意使用 1,2,3 而不是是，是的，是，您可以避免该宏。

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.

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.

I imagine that VLOOKUP is also involved somewhere within the if statement, and variables need to be defined to establish the range?

Would appreciate any help.

Thanks!

解决方案

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,3 instead of yes,yes,yes you can avoid the macro.

这篇关于EXCEL VBA - 循环通过列中的单元格（如果不为空），将单元格值打印到另一列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！