function won't work help

[ksnapp]

here is the code

Function step1(Names As Range, Name As String)
Dim C As Single ' counter
Dim N As Single ' number of rows from top

For Each cell In Names
If cell.Value = Name Then
Range("a1", cell).Select
N = Selection.Rows.Count
End If
Next

step1 = N
End Function

it just returns 1 if the value for Name is in the range Names
regardless of the position of the name in the range. If the value fo
name is not in the list it returns 0.

What im trying to do is get the number of the row that the speicifie
value for name occours in Names, or from the top either way will work

 

[JE McGimpsey]

Is there any reason you're not using

=IF(ISNA(MATCH(Name,Names,False)), 0, MATCH(Name,Names,False))

or, via programming:

Public Function step1(Names As Range, Name As String) As Long
On Error Resume Next
step1 = Application.Match(Name, Names, False)
If Err Then step1 = 0
On Error GoTo 0
End Function

 

[mudraker]

N = Selection.Rows.Count counts the number of rows selected by th
previous comand which is always going to be one


try this to count how many times cell.value = name value


Function step1(Names As Range, Name As String)
Dim C As Single ' counter
Dim N As Single ' number of rows from top

For Each cell In Names
If cell.Value = Name Then
N = n + 1
End If
Next

step1 = N
End Function


or to get the row number try

Function step1(Names As Range, Name As String)
Dim C As Single ' counter
Dim N As Single ' number of rows from top

For Each cell In Names
If cell.Value = Name Then
Range("a1", cell).Select
N = cell.row
End If
Next

step1 = N
End Function



code not teste