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
|