Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select Case - issue
Hi,
I am trying to search through Column A for group name and match this with persons name in col B then output a value in column C. Here is my macro thus far. Note same person can appear in both groups - help needed & appreciated Sub Group_Locate() Dim Counter As Integer Dim strFund As String Counter = 1 strFund = Cells(Counter, 1).Value While strFund < Empty And Counter < 5000 Select Case strFund Case "GroupA" And Cells(Counter, Counter + 1) = "Terence Darby": Cells(Counter, Counter + 3).Value = 15 'Case "GroupB" And Cells(Counter, Counter + 1) = "Jonny Butler": Cells(Counter, Counter + 3).Value = 22 'Case "GroupA" And Cells(Counter, Counter + 1) = "Jonny Butler": Cells(Counter, Counter + 3).Value = 61 Case Else: End Select Counter = Counter + 1 strFund = Cells(Counter, 4).Value Wend End Sub E.g run macro over belowData: Cell A1 = GroupA Cell B1 = Terence Darby Cell A2 = Group A Cell B2 = Jonny Butler Result in Col C1 = 15 Result in Col C2 = 61 Thanks George |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select Case - issue
If there are many selections in Column B and/or many Groups), then this is
probably not a good solution as you will have many IF (Case)statements. VLOOKUP may be a better alternative if the above is true. Sub Group_Locate() Dim Counter As Integer Dim strFund As String Counter = 1 strFund = Cells(Counter, 1).Value While strFund < "" And Counter < 5000 Select Case strFund Case "GroupA" If Cells(Counter, 2) = "Terence Darby" Then Cells(Counter, 3).Value = 15 Else If Cells(Counter, 2) = "Jonny Butler" Then Cells(Counter, 3).Value = 61 End If End If Case "GroupB" If Cells(Counter, 2) = "Jonny Butler" Then Cells(Counter, 3).Value = 22 End If End Select Counter = Counter + 1 strFund = Cells(Counter, 1).Value Wend End Sub "George" wrote: Hi, I am trying to search through Column A for group name and match this with persons name in col B then output a value in column C. Here is my macro thus far. Note same person can appear in both groups - help needed & appreciated Sub Group_Locate() Dim Counter As Integer Dim strFund As String Counter = 1 strFund = Cells(Counter, 1).Value While strFund < Empty And Counter < 5000 Select Case strFund Case "GroupA" And Cells(Counter, Counter + 1) = "Terence Darby": Cells(Counter, Counter + 3).Value = 15 'Case "GroupB" And Cells(Counter, Counter + 1) = "Jonny Butler": Cells(Counter, Counter + 3).Value = 22 'Case "GroupA" And Cells(Counter, Counter + 1) = "Jonny Butler": Cells(Counter, Counter + 3).Value = 61 Case Else: End Select Counter = Counter + 1 strFund = Cells(Counter, 4).Value Wend End Sub E.g run macro over belowData: Cell A1 = GroupA Cell B1 = Terence Darby Cell A2 = Group A Cell B2 = Jonny Butler Result in Col C1 = 15 Result in Col C2 = 61 Thanks George |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select Case - issue
Thanks Toppers,
yes there are many groups & 200 names. Can you suggest a lookup that can do what i am proposing below? Thanks George "Toppers" wrote: If there are many selections in Column B and/or many Groups), then this is probably not a good solution as you will have many IF (Case)statements. VLOOKUP may be a better alternative if the above is true. Sub Group_Locate() Dim Counter As Integer Dim strFund As String Counter = 1 strFund = Cells(Counter, 1).Value While strFund < "" And Counter < 5000 Select Case strFund Case "GroupA" If Cells(Counter, 2) = "Terence Darby" Then Cells(Counter, 3).Value = 15 Else If Cells(Counter, 2) = "Jonny Butler" Then Cells(Counter, 3).Value = 61 End If End If Case "GroupB" If Cells(Counter, 2) = "Jonny Butler" Then Cells(Counter, 3).Value = 22 End If End Select Counter = Counter + 1 strFund = Cells(Counter, 1).Value Wend End Sub "George" wrote: Hi, I am trying to search through Column A for group name and match this with persons name in col B then output a value in column C. Here is my macro thus far. Note same person can appear in both groups - help needed & appreciated Sub Group_Locate() Dim Counter As Integer Dim strFund As String Counter = 1 strFund = Cells(Counter, 1).Value While strFund < Empty And Counter < 5000 Select Case strFund Case "GroupA" And Cells(Counter, Counter + 1) = "Terence Darby": Cells(Counter, Counter + 3).Value = 15 'Case "GroupB" And Cells(Counter, Counter + 1) = "Jonny Butler": Cells(Counter, Counter + 3).Value = 22 'Case "GroupA" And Cells(Counter, Counter + 1) = "Jonny Butler": Cells(Counter, Counter + 3).Value = 61 Case Else: End Select Counter = Counter + 1 strFund = Cells(Counter, 4).Value Wend End Sub E.g run macro over belowData: Cell A1 = GroupA Cell B1 = Terence Darby Cell A2 = Group A Cell B2 = Jonny Butler Result in Col C1 = 15 Result in Col C2 = 61 Thanks George |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select Case - issue
George,
One possible solution. This assumes a table in Sheet2 in colums A-C containing Group, Name and Value sorted by Group. The function finds the Group (first record of) and then searches the names in that group to find the value. If a Group or Name isn't found, the function returns a value of 0. HTH Function GetValue(ByVal Group As String, ByVal Name As String) As Integer Dim rnga As Range Dim lastrow As Long, n As Integer Dim row, code With Worksheets("Sheet2") '<=== change as required lastrow = .Cells(Rows.Count, "A").End(xlUp).row Set rnga = .Range("A1:A" & lastrow) row = Application.Match(Group, rnga, 0) ' first record forthis group .... If IsError(row) Then MsgBox Group & " was not found" GetValue = 0 Exit Function Else ' look for value for this name ........ n = Application.CountIf(rnga, Group) ' number of records in this group code = Application.VLookup(Name, .Range(.Cells(row, "B"), .Cells(row + n - 1, "C")), 2, False) If IsError(code) Then MsgBox Name & " was not found" GetValue = 0 Exit Function End If End If End With GetValue = code End Function Sub Group_Locate() Dim Counter As Integer Dim strFund As String Counter = 1 With Worksheets("Sheet1") <=== change as required While .Cells(Counter, "A") < "" Or Counter < 5000 .Cells(Counter, 3) = GetValue(.Cell(Counter, "A"), .Cells(Counter, "B")) Counter = Counter + 1 Wend End With End Sub "George" wrote: Hi, I am trying to search through Column A for group name and match this with persons name in col B then output a value in column C. Here is my macro thus far. Note same person can appear in both groups - help needed & appreciated Sub Group_Locate() Dim Counter As Integer Dim strFund As String Counter = 1 strFund = Cells(Counter, 1).Value While strFund < Empty And Counter < 5000 Select Case strFund Case "GroupA" And Cells(Counter, Counter + 1) = "Terence Darby": Cells(Counter, Counter + 3).Value = 15 'Case "GroupB" And Cells(Counter, Counter + 1) = "Jonny Butler": Cells(Counter, Counter + 3).Value = 22 'Case "GroupA" And Cells(Counter, Counter + 1) = "Jonny Butler": Cells(Counter, Counter + 3).Value = 61 Case Else: End Select Counter = Counter + 1 strFund = Cells(Counter, 4).Value Wend End Sub E.g run macro over belowData: Cell A1 = GroupA Cell B1 = Terence Darby Cell A2 = Group A Cell B2 = Jonny Butler Result in Col C1 = 15 Result in Col C2 = 61 Thanks George |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select Case - issue
Thanks very much Toppers,
One question: Is the function run on data in sheet 1 then searches sheet 2 for matches? Cheers "Toppers" wrote: George, One possible solution. This assumes a table in Sheet2 in colums A-C containing Group, Name and Value sorted by Group. The function finds the Group (first record of) and then searches the names in that group to find the value. If a Group or Name isn't found, the function returns a value of 0. HTH Function GetValue(ByVal Group As String, ByVal Name As String) As Integer Dim rnga As Range Dim lastrow As Long, n As Integer Dim row, code With Worksheets("Sheet2") '<=== change as required lastrow = .Cells(Rows.Count, "A").End(xlUp).row Set rnga = .Range("A1:A" & lastrow) row = Application.Match(Group, rnga, 0) ' first record forthis group .... If IsError(row) Then MsgBox Group & " was not found" GetValue = 0 Exit Function Else ' look for value for this name ........ n = Application.CountIf(rnga, Group) ' number of records in this group code = Application.VLookup(Name, .Range(.Cells(row, "B"), .Cells(row + n - 1, "C")), 2, False) If IsError(code) Then MsgBox Name & " was not found" GetValue = 0 Exit Function End If End If End With GetValue = code End Function Sub Group_Locate() Dim Counter As Integer Dim strFund As String Counter = 1 With Worksheets("Sheet1") <=== change as required While .Cells(Counter, "A") < "" Or Counter < 5000 .Cells(Counter, 3) = GetValue(.Cell(Counter, "A"), .Cells(Counter, "B")) Counter = Counter + 1 Wend End With End Sub "George" wrote: Hi, I am trying to search through Column A for group name and match this with persons name in col B then output a value in column C. Here is my macro thus far. Note same person can appear in both groups - help needed & appreciated Sub Group_Locate() Dim Counter As Integer Dim strFund As String Counter = 1 strFund = Cells(Counter, 1).Value While strFund < Empty And Counter < 5000 Select Case strFund Case "GroupA" And Cells(Counter, Counter + 1) = "Terence Darby": Cells(Counter, Counter + 3).Value = 15 'Case "GroupB" And Cells(Counter, Counter + 1) = "Jonny Butler": Cells(Counter, Counter + 3).Value = 22 'Case "GroupA" And Cells(Counter, Counter + 1) = "Jonny Butler": Cells(Counter, Counter + 3).Value = 61 Case Else: End Select Counter = Counter + 1 strFund = Cells(Counter, 4).Value Wend End Sub E.g run macro over belowData: Cell A1 = GroupA Cell B1 = Terence Darby Cell A2 = Group A Cell B2 = Jonny Butler Result in Col C1 = 15 Result in Col C2 = 61 Thanks George |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select Case - issue
George,
The function (called from the loop in Group_Locate module ) has input from "Sheet1" i.e Group and Name, and looks at the table (Group, Name and Code) in "Sheet2" to get the Code (Numeric value). The macros themselves should be placed in a general module. HTH "George" wrote: Thanks very much Toppers, One question: Is the function run on data in sheet 1 then searches sheet 2 for matches? Cheers "Toppers" wrote: George, One possible solution. This assumes a table in Sheet2 in colums A-C containing Group, Name and Value sorted by Group. The function finds the Group (first record of) and then searches the names in that group to find the value. If a Group or Name isn't found, the function returns a value of 0. HTH Function GetValue(ByVal Group As String, ByVal Name As String) As Integer Dim rnga As Range Dim lastrow As Long, n As Integer Dim row, code With Worksheets("Sheet2") '<=== change as required lastrow = .Cells(Rows.Count, "A").End(xlUp).row Set rnga = .Range("A1:A" & lastrow) row = Application.Match(Group, rnga, 0) ' first record forthis group .... If IsError(row) Then MsgBox Group & " was not found" GetValue = 0 Exit Function Else ' look for value for this name ........ n = Application.CountIf(rnga, Group) ' number of records in this group code = Application.VLookup(Name, .Range(.Cells(row, "B"), .Cells(row + n - 1, "C")), 2, False) If IsError(code) Then MsgBox Name & " was not found" GetValue = 0 Exit Function End If End If End With GetValue = code End Function Sub Group_Locate() Dim Counter As Integer Dim strFund As String Counter = 1 With Worksheets("Sheet1") <=== change as required While .Cells(Counter, "A") < "" Or Counter < 5000 .Cells(Counter, 3) = GetValue(.Cell(Counter, "A"), .Cells(Counter, "B")) Counter = Counter + 1 Wend End With End Sub "George" wrote: Hi, I am trying to search through Column A for group name and match this with persons name in col B then output a value in column C. Here is my macro thus far. Note same person can appear in both groups - help needed & appreciated Sub Group_Locate() Dim Counter As Integer Dim strFund As String Counter = 1 strFund = Cells(Counter, 1).Value While strFund < Empty And Counter < 5000 Select Case strFund Case "GroupA" And Cells(Counter, Counter + 1) = "Terence Darby": Cells(Counter, Counter + 3).Value = 15 'Case "GroupB" And Cells(Counter, Counter + 1) = "Jonny Butler": Cells(Counter, Counter + 3).Value = 22 'Case "GroupA" And Cells(Counter, Counter + 1) = "Jonny Butler": Cells(Counter, Counter + 3).Value = 61 Case Else: End Select Counter = Counter + 1 strFund = Cells(Counter, 4).Value Wend End Sub E.g run macro over belowData: Cell A1 = GroupA Cell B1 = Terence Darby Cell A2 = Group A Cell B2 = Jonny Butler Result in Col C1 = 15 Result in Col C2 = 61 Thanks George |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select Case - issue
Hey Toppers
I am sorry to be totally dependent on your help but i am having trouble and am not sure how to set out the code in the module. I have tried naming the macro Group_Locate and putting The function under that but it is not working. I am a novice and am not sure where to put the function code. George "Toppers" wrote: George, The function (called from the loop in Group_Locate module ) has input from "Sheet1" i.e Group and Name, and looks at the table (Group, Name and Code) in "Sheet2" to get the Code (Numeric value). The macros themselves should be placed in a general module. HTH "George" wrote: Thanks very much Toppers, One question: Is the function run on data in sheet 1 then searches sheet 2 for matches? Cheers "Toppers" wrote: George, One possible solution. This assumes a table in Sheet2 in colums A-C containing Group, Name and Value sorted by Group. The function finds the Group (first record of) and then searches the names in that group to find the value. If a Group or Name isn't found, the function returns a value of 0. HTH Function GetValue(ByVal Group As String, ByVal Name As String) As Integer Dim rnga As Range Dim lastrow As Long, n As Integer Dim row, code With Worksheets("Sheet2") '<=== change as required lastrow = .Cells(Rows.Count, "A").End(xlUp).row Set rnga = .Range("A1:A" & lastrow) row = Application.Match(Group, rnga, 0) ' first record forthis group .... If IsError(row) Then MsgBox Group & " was not found" GetValue = 0 Exit Function Else ' look for value for this name ........ n = Application.CountIf(rnga, Group) ' number of records in this group code = Application.VLookup(Name, .Range(.Cells(row, "B"), .Cells(row + n - 1, "C")), 2, False) If IsError(code) Then MsgBox Name & " was not found" GetValue = 0 Exit Function End If End If End With GetValue = code End Function Sub Group_Locate() Dim Counter As Integer Dim strFund As String Counter = 1 With Worksheets("Sheet1") <=== change as required While .Cells(Counter, "A") < "" Or Counter < 5000 .Cells(Counter, 3) = GetValue(.Cell(Counter, "A"), .Cells(Counter, "B")) Counter = Counter + 1 Wend End With End Sub "George" wrote: Hi, I am trying to search through Column A for group name and match this with persons name in col B then output a value in column C. Here is my macro thus far. Note same person can appear in both groups - help needed & appreciated Sub Group_Locate() Dim Counter As Integer Dim strFund As String Counter = 1 strFund = Cells(Counter, 1).Value While strFund < Empty And Counter < 5000 Select Case strFund Case "GroupA" And Cells(Counter, Counter + 1) = "Terence Darby": Cells(Counter, Counter + 3).Value = 15 'Case "GroupB" And Cells(Counter, Counter + 1) = "Jonny Butler": Cells(Counter, Counter + 3).Value = 22 'Case "GroupA" And Cells(Counter, Counter + 1) = "Jonny Butler": Cells(Counter, Counter + 3).Value = 61 Case Else: End Select Counter = Counter + 1 strFund = Cells(Counter, 4).Value Wend End Sub E.g run macro over belowData: Cell A1 = GroupA Cell B1 = Terence Darby Cell A2 = Group A Cell B2 = Jonny Butler Result in Col C1 = 15 Result in Col C2 = 61 Thanks George |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select Case - issue
George,
Send me the workbook and I'll insert the code ). OR Open your workbook and press ALT+F11 which will open the VISUAL BASIC EDITOR (VBE). On the left-hand side you should the Project panel and the last entry will be your worbook (VBAProject ( <bookname) with a list of your sheets. Right-click on any of these and do INSERT==Module: a new entry "Module 1" will be created. Copy and paste all the code I sent into this (blank area of screen on your right). Put your cursor somewhere in the "Group_Locate" code and click the green "arrow head" (run sub/userform") on the (usually) first toolbar in the VBE. this will execute the macro. Be sure you have set up the table on Sheet2 (or your equivalent). HTH "George" wrote: Hey Toppers I am sorry to be totally dependent on your help but i am having trouble and am not sure how to set out the code in the module. I have tried naming the macro Group_Locate and putting The function under that but it is not working. I am a novice and am not sure where to put the function code. George "Toppers" wrote: George, The function (called from the loop in Group_Locate module ) has input from "Sheet1" i.e Group and Name, and looks at the table (Group, Name and Code) in "Sheet2" to get the Code (Numeric value). The macros themselves should be placed in a general module. HTH "George" wrote: Thanks very much Toppers, One question: Is the function run on data in sheet 1 then searches sheet 2 for matches? Cheers "Toppers" wrote: George, One possible solution. This assumes a table in Sheet2 in colums A-C containing Group, Name and Value sorted by Group. The function finds the Group (first record of) and then searches the names in that group to find the value. If a Group or Name isn't found, the function returns a value of 0. HTH Function GetValue(ByVal Group As String, ByVal Name As String) As Integer Dim rnga As Range Dim lastrow As Long, n As Integer Dim row, code With Worksheets("Sheet2") '<=== change as required lastrow = .Cells(Rows.Count, "A").End(xlUp).row Set rnga = .Range("A1:A" & lastrow) row = Application.Match(Group, rnga, 0) ' first record forthis group .... If IsError(row) Then MsgBox Group & " was not found" GetValue = 0 Exit Function Else ' look for value for this name ........ n = Application.CountIf(rnga, Group) ' number of records in this group code = Application.VLookup(Name, .Range(.Cells(row, "B"), .Cells(row + n - 1, "C")), 2, False) If IsError(code) Then MsgBox Name & " was not found" GetValue = 0 Exit Function End If End If End With GetValue = code End Function Sub Group_Locate() Dim Counter As Integer Dim strFund As String Counter = 1 With Worksheets("Sheet1") <=== change as required While .Cells(Counter, "A") < "" Or Counter < 5000 .Cells(Counter, 3) = GetValue(.Cell(Counter, "A"), .Cells(Counter, "B")) Counter = Counter + 1 Wend End With End Sub "George" wrote: Hi, I am trying to search through Column A for group name and match this with persons name in col B then output a value in column C. Here is my macro thus far. Note same person can appear in both groups - help needed & appreciated Sub Group_Locate() Dim Counter As Integer Dim strFund As String Counter = 1 strFund = Cells(Counter, 1).Value While strFund < Empty And Counter < 5000 Select Case strFund Case "GroupA" And Cells(Counter, Counter + 1) = "Terence Darby": Cells(Counter, Counter + 3).Value = 15 'Case "GroupB" And Cells(Counter, Counter + 1) = "Jonny Butler": Cells(Counter, Counter + 3).Value = 22 'Case "GroupA" And Cells(Counter, Counter + 1) = "Jonny Butler": Cells(Counter, Counter + 3).Value = 61 Case Else: End Select Counter = Counter + 1 strFund = Cells(Counter, 4).Value Wend End Sub E.g run macro over belowData: Cell A1 = GroupA Cell B1 = Terence Darby Cell A2 = Group A Cell B2 = Jonny Butler Result in Col C1 = 15 Result in Col C2 = 61 Thanks George |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to use spellnumber formula | Excel Worksheet Functions | |||
Currency to Text | Excel Worksheet Functions | |||
How to .. | Excel Discussion (Misc queries) | |||
convert value in word. For Exampe Rs.115.00 convert into word as . | Excel Discussion (Misc queries) | |||
Is there a formula to spell out a number in excel? | Excel Worksheet Functions |