Try something more like this...

Private Sub Workbook_Open()
dim rngToSearch as range
dim rng as range

with sheets("Sheet1")
set rngtosearch = .range(.range("A2"), .cells(rows.count, "A").end(xlup))
end with

for each rng in rngtosearch
select case rng.Value =
case "Emp1"
cells(rng.row, "N").Value = "TeamA"
case "Emp1", "Emp2"
cells(rng.row, "N").Value = "TeamB"
case "Emp3"
cells(rng.row, "N").Value = "TeamC"
case else
cells(rng.row, "N").Value = "No Team"
end select
next rng
End Sub

--
HTH...

Jim Thomlinson


"bjohn" wrote:

I have a report generated from a program exported into excel upon completion.
When that workbook opens (only using one worksheet named Data), I want to
have the values in column N (Teams) populate based on values in the same row
in column A (Employee). Essentially I have 30 employees broken up into 7
teams.

Since I need to have Column A look at up to 30 values to return column N
values, should I run an If Then or Case, or something else entirely? My Excel
and VBA is very rusty. Need help with correct commands and basic structure,
please.

Code I have now is:

Private Sub Workbook_Open()
If Range("$a2").Value = "Emp1" Then
Range("$n2").Value = "TeamA"
ElseIf Range("$a2").Value = "Emp2" Then
Range("$n2").Value = "TeamB"
ElseIf Range("$a2").Value = "Emp3" Then
Range("$n2").Value = "TeamB"
ElseIf Range("$a2").Value = "" Then
Range("$n2").Value = ""
Else: Range("$n2").Value = "No Team"
End If

End Sub

Code works for row 2 but then stops. I've tried using both absolute and
relative referencing to no avail. Any help is appreciated