VBA to search multiple names in Column B, return value in Column A

[Beh162]

Right now I have it setup to look in column B for a name and then return a value in Column A directly next to it. I have it working for one name, but would I just continue this process over and over per name? Or is there a better approach?

Sub info()
Dim i As Long
For i = 2 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    If ActiveSheet.Cells(i, 2) = "Doe, John" Then
        ActiveSheet.Range("A" & i) = "Clothing"
    End If
Next i
End Sub

 

[davesexcel]

You should keep the list of names on a worksheet, the you can loop through the list.
If you hard code the names in the code, you will always be editing the code.

Prepare a test sheet like the following

Add a button and use this code.

Sub Button1_Click()
    Dim rng As Range, c As Range
    Dim Frng As Range, f As Range

    Set rng = Range("P2:P5")
    Set Frng = Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)

    For Each c In rng.Cells
        For Each f In Frng.Cells

            If f = c Then f.Offset(, -1) = c.Offset(, 1)

        Next f
    Next c

End Sub

 

[Beh162]

You should keep the list of names on a worksheet, the you can loop through the list.
If you hard code the names in the code, you will always be editing the code.

Prepare a test sheet like the following

View attachment 22232

Add a button and use this code.

Wouldn't I need to create a table each time I download the new data?

 

[Beh162]

Just to clarify I download an excel sheet each day that has data populated and I run my saved VBA's. One of my sheets there are a few people that get their department manually changed each time. It's probably a group of 10 people at most that change to say "Clothing" I see what you mean if someone is then added, I would need to add them to the VBA or remove.