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
|