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

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

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

Hello,

with an array formula (use Ctrl+Alt+Enter instead of Enter to validate the
formula)
=INDEX($B:$B,MIN(IF($A$1:$A$7="DEK S01",
IF($B$1:$B$70,ROW($A$1:$A$7),10000000),10000000)) )


$A$1:$A$7 is the first column of your data
"DEK S01" is the string you to be searched
$B$1:$B$7 is the second column of your data

The formula return an error #REF! if no values are 0






"DG" a écrit dans le message de
...
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

Sorry,
replace 0 with <0 to get the first negative or positive value.


"Charabeuh" a écrit dans le message de
...
Hello,

with an array formula (use Ctrl+Alt+Enter instead of Enter to validate the
formula)
=INDEX($B:$B,MIN(IF($A$1:$A$7="DEK S01",
IF($B$1:$B$70,ROW($A$1:$A$7),10000000),10000000)) )


$A$1:$A$7 is the first column of your data
"DEK S01" is the string you to be searched
$B$1:$B$7 is the second column of your data

The formula return an error #REF! if no values are 0






"DG" a écrit dans le message de
...
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

Ignore the posted code and use this instead.
If the range is sorted you can use a binary search and that is a lot faster.
Bear in mind that the binary search only has been used in the array method
here, not the range method.

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

Sub test()

Dim i As Long
Dim vResult
Dim lIndex As Long
Dim rng1 As Range
Dim rng2 As Range
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, "AC01B1", bSortedLookup, , lIndex)
Else
vResult = GetFirstNotZero(rng1, "AC01B1", bSortedLookup, , lIndex)
End If

StopSW , "using array = " & bUseArray

MsgBox "result = " & vResult & vbCrLf & _
"Index = " & lIndex, , _
"using array = " & bUseArray

End Sub

Sub FillRange(bSort As Boolean)

Dim i As Long
Dim arr1
Dim arr2

arr1 = Array("B", "C", "D", 0, 1, 2)
arr2 = Array(0, 0, 0, 0, 0, 1, 2, 3, 4, 5)

For i = 1 To 65536
Cells(i, 1) = "A" & _
arr1(CLng(5 * Rnd)) & _
arr1(CLng(5 * Rnd)) & _
arr1(CLng(5 * Rnd)) & _
arr1(CLng(5 * Rnd)) & _
arr1(CLng(5 * 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, _
Optional lReturnIndex As Long) 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
lReturnIndex = c.Row
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
lReturnIndex = c.Row
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, _
Optional lReturnIndex As Long) As Variant

Dim i As Long
Dim lStartRow As Long

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

If bSortedLookup Then

lStartRow = BinarySearchVariant(vValue, arr, 1, lNotFoundReturn)

If lStartRow = lNotFoundReturn Then
GetFirstNotZero2 = lNotFoundReturn
Exit Function
End If

Do While arr(lStartRow, 1) = vValue
lStartRow = lStartRow - 1
Loop

For i = lStartRow To UBound(arr)
'Stop
If arr(i, 1) = vValue Then
If arr(i, 2) < 0 Then
lReturnIndex = i
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
lReturnIndex = i
GetFirstNotZero2 = arr(i, 2)
Exit Function
End If
End If
Next i
End If

GetFirstNotZero2 = lNotFoundReturn

End Function

Function BinarySearchVariant(vLookFor As Variant, _
vaArray As Variant, _
lColumn As Long, _
Optional lNotFound As Long = -1) As Long

Dim lLow As Long
Dim lMid As Long
Dim lHigh As Long

On Error GoTo PTR_Exit

'Assume we didn't find it
BinarySearchVariant = lNotFound

'Get the starting positions
lLow = LBound(vaArray)
lHigh = UBound(vaArray)

Do
'Find the midpoint of the array
lMid = (lLow + lHigh) \ 2

If vaArray(lMid, lColumn) = vLookFor Then
'We found it, so return the location and quit
BinarySearchVariant = lMid
Exit Do

ElseIf vaArray(lMid, lColumn) vLookFor Then
'The midpoint item is bigger than us - throw away the top half
lHigh = lMid - 1
Else
'The midpoint item is smaller than us - throw away the bottom half
lLow = lMid + 1
End If

'Continue until our pointers cross
Loop Until lLow lHigh

PTR_Exit:

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



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