2 things to bear in mind he

1. Is the data in column 1 sorted or not?
If it is then you can do an early exit from the loop once the value in
column 1 is greater than the lookup value.
Obviously this saves a lot of time.
2. Looping through an array is a lot faster than looping through a range.
This gets even more important if you
have to do the lookup multiple times as the array has to be made only once.

The following testing code demonstrates both.
Copy the whole lot and paste to a module, then run test.

Option Explicit
Private arr As Variant
Private bFilledRange As Boolean
Private bMadeArray As Boolean
Private lStartTime As Long
Private Declare Function timeGetTime Lib "winmm.dll" () As Long

Sub test()

Dim i As Long
Dim vResult
Dim rng1 As Range
Dim rng2 As Range
Dim bSortedLookup As Boolean
Dim bUseArray As Boolean

If bFilledRange = False Then
If MsgBox("Have the lookup column sorted?", vbYesNo, _
"looking up value") = vbYes Then
bSortedLookup = True
End If
FillRange bSortedLookup
bFilledRange = True
End If

Set rng1 = Range(Cells(1), Cells(65536, 1))
Set rng2 = Range(Cells(1), Cells(65536, 2))

If MsgBox("Use the array function?", vbYesNo, _
"looking up value") = vbYes Then
bUseArray = True
End If

StartSW

If bUseArray Then
vResult = GetFirstNotZero2(rng2, "AC00BD", bSortedLookup)
Else
vResult = GetFirstNotZero(rng1, "AC00BD", bSortedLookup)
End If

StopSW , "using array = " & bUseArray

MsgBox "result = " & vResult, , "using array = " & bUseArray

End Sub

Sub FillRange(bSort As Boolean)

Dim i As Long
Dim arr1
Dim arr2

arr1 = Array("A", "B", "C", "D", 0, 1, 2, 3)
arr2 = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9)

For i = 1 To 65536
Cells(i, 1) = "A" & _
arr1(CLng(7 * Rnd)) & _
arr1(CLng(7 * Rnd)) & _
arr1(CLng(7 * Rnd)) & _
arr1(CLng(7 * Rnd)) & _
arr1(CLng(7 * Rnd))
Cells(i, 2) = arr2(CLng(9 * Rnd))
Next i

If bSort Then
Range(Cells(1), Cells(65536, 1)).Sort Key1:=Cells(1), _
Order1:=xlAscending, _
Header:=xlNo
End If

End Sub

Function GetFirstNotZero(rng As Range, _
vValue As Variant, _
bSortedLookup As Boolean, _
Optional lNotFoundReturn As Long = -1) As Variant

Dim c As Range

If bSortedLookup Then
For Each c In rng
If c.Value = vValue Then
If c.Offset(0, 1).Value < 0 Then
GetFirstNotZero = c.Offset(0, 1).Value
Exit Function
End If
Else
If c.Value vValue Then
Exit For
End If
End If
Next c
Else
For Each c In rng
If c.Value = vValue Then
If c.Offset(0, 1).Value < 0 Then
GetFirstNotZero = c.Offset(0, 1).Value
Exit Function
End If
End If
Next c
End If

GetFirstNotZero = lNotFoundReturn

End Function

Function GetFirstNotZero2(rng As Range, _
vValue As Variant, _
bSortedLookup As Boolean, _
Optional lNotFoundReturn As Long = -1) As Variant

Dim i As Long

If bMadeArray = False Then
arr = rng
bMadeArray = True
End If

If bSortedLookup Then
For i = 1 To UBound(arr)
If arr(i, 1) = vValue Then
If arr(i, 2) < 0 Then
GetFirstNotZero2 = arr(i, 2)
Exit Function
End If
Else
If arr(i, 1) vValue Then
Exit For
End If
End If
Next i
Else
For i = 1 To UBound(arr)
If arr(i, 1) = vValue Then
If arr(i, 2) < 0 Then
GetFirstNotZero2 = arr(i, 2)
Exit Function
End If
End If
Next i
End If

GetFirstNotZero2 = lNotFoundReturn

End Function

Sub StartSW()
lStartTime = timeGetTime()
End Sub

Function StopSW(Optional bMsgBox As Boolean = True, _
Optional vMessage As Variant, _
Optional lMinimumTimeToShow As Long = -1) As Variant

Dim lTime As Long

lTime = timeGetTime() - lStartTime

If lTime lMinimumTimeToShow Then
If IsMissing(vMessage) Then
StopSW = lTime
Else
StopSW = lTime & " - " & vMessage
End If
End If

If bMsgBox Then
If lTime lMinimumTimeToShow Then
MsgBox "Done in " & lTime & " msecs", , vMessage
End If
End If

End Function



RBS



"DG" wrote in message
...
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