Select Case - issue

[Guest]

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

 

[Guest]

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

 

[Guest]

Thanks Toppers,
yes there are many groups & 200 names.
Can you suggest a lookup that can do what i am proposing below?
Thanks
George

 

[Guest]

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

 

[Guest]

Thanks very much Toppers,
One question:
Is the function run on data in sheet 1 then searches sheet 2 for matches?
Cheers

 

[Guest]

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

 

[Guest]

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

 

[Guest]

George,
Send me the workbook and I'll insert the code
([email protected]).

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