IF, Case, or something else

[bjohn]

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

 

[Jim Thomlinson]

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

 

[LeShark]

assuming your file is not "created" by the external program - i.e. you can
put code etc into the sheet and it will not be overwritten then.....

Setup a 2-column range somewhere and list all the employees is 1st column
and team name in 2nd column. call the range "myteams"

insert the following name in the sheet - call it mynames
this assumes that your "input" names start in Col A row 2

=OFFSET('sheet1'!$A$2,0,0,COUNTA('sheet2!$A:$A),1)

insert a name for Cell A1 - call it "top"

***************

Sub auto_open()
Range("top").Select

Dim name As String
Dim r As Variant

For Each r In Range("mynames")

ActiveCell.Offset(1, 0).Select
name = ActiveCell.Offset(0, 0).Value
ActiveCell.Offset(0, 13).Value = Application.VLookup(name, Range("myteams"),
2, 0)

Next

End Sub

****************
this will populate column "N" for all names based on the values in your table

 

[bjohn]

Thanks Jim. That's what I was looking for. When I was tested it without
actually running the report, it did exactly what I was looking for. When I
actually ran the report, I received this error:

Method 'Rows' of object'_Global' failed

When I go to debug, it stops on:
set rngtosearch = .range(.range("A2"), .cells(rows.count,
"A").end(xlup))

I used your code replacing the names of the worksheet, employees, and team
names, nothing else.

Any thoughts?
Thanks,
Ben