2

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!

Rook
  • 5,734
  • 3
  • 34
  • 43
HappyMango
  • 23
  • 1
  • 1
  • 4
  • 1
    You can do this with a worksheet formula. `D2=IF(B2="YES",A2,"")`. Otherwise it's a relatively simple macro, but you should learn how basic control structures work before using code you don't understand. – cheezsteak Nov 18 '14 at 18:10
  • Yes, I know this is an old question, but the formula @cheezsteak gave doesn't exactly reach the OP's desired result. I believe this one should do it, copied down from D2, of course: `=IFERROR(OFFSET(A$1,IF(OFFSET(C2,-1,0)=$C$1,1,MATCH(OFFSET(C2,-1,0),A:A,0))-1+MATCH("Yes",OFFSET(B$1,IF(OFFSET(C2,-1,0)=$C$1,1,MATCH(OFFSET(C2,-1,0),A:A,0)),0,COUNTA(A:A)),0),0,1),"")` – GlennFromIowa May 16 '18 at 22:20

2 Answers2

10

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.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
3

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
SierraOscar
  • 17,507
  • 6
  • 40
  • 68