Hi

I think you have to loop, but you can exit the loop when you find the first
match:

Public Function Get_Cost(SearchRange As Range, FindText As String)
Dim f As Range
For Each cell In SearchRange
If cell.Value = FindText Then
Set f = cell
Exit For
End If
Next
If f Is Nothing Then
'text is not found
Get_Cost = CVErr(2042)
Exit Function
End If
If f.Offset(0, 1) = 0 Then
Do
Set f = f.Offset(1, 0)
Loop Until f.Offset(0, 1) < 0
End If
Get_Cost = f.Offset(0, 1).Value
End Function

Regards,
Per


"DG" skrev i meddelelsen
...
I have a spreadsheet as follows:

column A column B
DEK S01 0
DEK S01 300
DEK S01 400
DEK S01 450
LIC G13 25
LIC G13 50
LIC G14 100
etc...

I am trying to write a function to return the first non-zero value in
column B of the Item in column A. So when I call the function
Get_Cost("DEK S01") it will return 300. When I call Get_Cost("LIC G13")
it will return 25.

A quick way of getting the row number for LIC G13 would be a help, so I
don't have to loop so much.

DG