IF, Case, or something else
 

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

IF, Case, or something else
 

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

IF, Case, or something else
 

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" 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

IF, Case, or something else
 

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

"Jim Thomlinson" wrote:

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