Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change data of entire column from small case to upper case | Excel Worksheet Functions | |||
Changing multiple cell text from lower case to upper case | Excel Discussion (Misc queries) | |||
How to change mixed case to upper case in Excel for all cells | Excel Discussion (Misc queries) | |||
Change the text from lower case to upper case in an Excel work boo | Excel Discussion (Misc queries) | |||
How do I change a column in Excel from upper case to lower case? | Excel Worksheet Functions |