Hello,

I have the following code

Function Price(Code, Table)
Price = WorksheetFunction.VLookup(Code, Table, 18, False)
End Function

am using this function in order to get from the 18th column the price of a
specific product with Code. The codes of the different products are listed in
the 1st column. On the 1st column the codes of the different products can be
repeated many times.
With this vba code I am using I can get the price of the first listed
product. But I want to get the last.
Can someone please help me solve this problem?

Thank you in advance.

Antonis,

Try this

Function Price(Code, Table)
Dim oCell As Range
Dim sFirst As String
Dim oLast As Range

With Table
Set oCell = .Find(Code)
If Not oCell Is Nothing Then
sFirst = oCell.Address
Do
Set oCell = .FindNext(oCell)
If Not oCell Is Nothing And oCell.Address < sFirst Then
Set oLast = oCell
End If
Loop While Not oCell Is Nothing And oCell.Address < sFirst
End If
End With
Price = oLast.Offset(0, 17).Value
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Antonis" wrote in message
...
Hello,

I have the following code

Function Price(Code, Table)
Price = WorksheetFunction.VLookup(Code, Table, 18, False)
End Function

am using this function in order to get from the 18th column the price of
a
specific product with Code. The codes of the different products are listed
in
the 1st column. On the 1st column the codes of the different products can
be
repeated many times.
With this vba code I am using I can get the price of the first listed
product. But I want to get the last.
Can someone please help me solve this problem?

Thank you in advance.

"Antonis" wrote in message
...
Hello,

I have the following code

Function Price(Code, Table)
Price = WorksheetFunction.VLookup(Code, Table, 18, False)
End Function

am using this function in order to get from the 18th column the price of a
specific product with Code. The codes of the different products are listed
in
the 1st column. On the 1st column the codes of the different products can
be
repeated many times.
With this vba code I am using I can get the price of the first listed
product. But I want to get the last.
Can someone please help me solve this problem?

Thank you in advance.

In a simple way:
------------------------------
Function Price(Code, Table)
Dim i As Long

For i = Table.Rows.Count To 1 Step -1
If Table(i, 1) = Code Then
Price = Table(i, 18)
Exit Function
End If
Next

End Function
-----------------------------

Ciao
Bruno

I am only loudly thinking

introduce a helper column and type serials 1,2,3, etc
sort(DSESCENDING) the database acordings to this column
and now try (not tested)




"Bruno Campanini" wrote in message
...
"Antonis" wrote in message
...
Hello,

I have the following code

Function Price(Code, Table)
Price = WorksheetFunction.VLookup(Code, Table, 18, False)
End Function

am using this function in order to get from the 18th column the price of
a
specific product with Code. The codes of the different products are
listed
in
the 1st column. On the 1st column the codes of the different products
can
be
repeated many times.
With this vba code I am using I can get the price of the first listed
product. But I want to get the last.
Can someone please help me solve this problem?

Thank you in advance.

In a simple way:
------------------------------
Function Price(Code, Table)
Dim i As Long

For i = Table.Rows.Count To 1 Step -1
If Table(i, 1) = Code Then
Price = Table(i, 18)
Exit Function
End If
Next

End Function
-----------------------------

Ciao
Bruno