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