Worksheet Function In VBA

[BoutToGoRAM]

So I'm trying to create a function in VBA to whenever I type in specific parameters to a function in excel that'll give me the outcome of those parameters.
For example, I have a list of names of people in one column; Collin, Vick, etc.... and I've got two other columns that have numbers that coincide with those names... The objective here is for the user to type in a function in any given cell with the name and have the spreadsheet output the number that goes with that name.

I'm using vlookup for this part and for some reason it keeps spitting out "Nothing". This is what I've got but I've hit a dead end because I've got no clue what to do.


Function KidsInClass(variety As String) As String
Dim StrOut


Application.Volatile

On Error Resume Next

StrOut = Application.WorksheetFunction.VLookup(variety, _
Sheets("Teachers with Kids").Range("B3874"), 3, False)

On Error GoTo 0

If IsEmpty(StrOut) Then
KidsInClass = "Nothing"
Else
KidsInClass = StrOut
End If
End Function

[snb]

Pleas, use code tags around VBA code !

sub M_snb()
   msgbox Sheets("Teachers with Kids").columns(2).find("Collin").offset(,1)
End Sub

[BoutToGoRAM]

Sorry, I didn't provide enough info. but there are a few duplicate names, so that wouldn't work

[snb]

That case vlookup nor match will work either.

[mikerickson]

What are the arguments that you want your UDF to have?
It sounds like Name is one of the arguments you want.
You might also want the range where the function should look for the name.
You would also want to indicate where the other columns are (and which to look in) to find the result that you want.
Since there are duplicates, how do you want the UDF to handle that? Do you want to specify whether you are talking about the first, second or third Tom with an argument? Or do you want to return the data for all the Tom's, combined in some fashion?