EXCEL VBA - Loop through cells in a column, if not empty, print cell value into another column

Question

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!

Solution 1

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.

Solution 2

Assuming you have headers in row 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