Lookup a name in a range of values??

[bggauth]

Hello,

This is a bit complicated for me.

I want to be able to do a search in Excel (click a button and enter a name) that will bring back a persons name that is associated with all tasks.

So the tasks are in column A.
User names are in Coumns B - J.
Let's say there are 100 tasks.
I need a formula that will do the following:
Bring back the number of times the person's name is listed or how many tasks he/she is assigned to.
Possibly also bring back the tasks he/she is assigned to.

Thansk for any ideas.

Barry

 

[StructuredWater]

Hi bggauth,

I am not confident with VBA but you might try something like:

Dim strToFind As String
Dim strMsg As String
Dim r As Range
strToFind = InputBox("Enter text to find")
For Each r In Intersect(Range("$B:$B"), ActiveSheet.UsedRange)
    If Not r.Resize(, 9).Find(strToFind, , , xlWhole, xlByRows) Is Nothing Then

        strMsg = strMsg & vbCrLf & strToFind & vbTab & Range("A" & r.Row).Value
    End If
Next r
MsgBox "Search item:" & vbTab & "Project:" & vbCrLf & strMsg

Note that when I tried using named arguments for the Find method (LookAt:=xlWhole,SearchOrder:=xlByRows) I got a Run Time Error 9: Subscript Out Of Range (therefore the commas for the missing arguments). Don't know why. Does anyone?

Kind regards,
JohnM