One way is the following; it sorts, extracts the array, then resorts:

Sub testab2001()
Dim arr(), rng As Range, iRows As Long, i As Long
Set rng = Range("A1:B4")
iRows = rng.Rows.Count
For i = 1 To iRows
rng(i, 3).Value = i
Next
x = Range(rng(1, 1), rng(iRows, 3)).Sort(rng(1, 1), xlDescending)
arr = rng
x = Range(rng(1, 1), rng(iRows, 3)).Sort(rng(1, 3), xlAscending)
Range(rng(1, 3), rng(iRows, 3)).ClearContents
End Sub

Alan Beban

Erche DP wrote:

Hi Mike,
Thanks a lot.
The idea is to have for example :
1.3 is the maximum value on column A
so, i want to have the value on column B which the same row as the 1.3
(which is yoghurt)
the LOOKUP method didn't help, because it should have those value sorted
first, while i can not have my real report sorted.
so, what do you suggest ?


A B
1.2 apple
1.3 yoghurt
1.1 mango
1.0 papaya


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!