Did you say "Looping?" ;) <vbg
Don't know if this is what you want. In a 2-dim array, this should return
an array of the 10 smallest numbers in the first column.
Sub Demo()
'// Dana DeLouis
Dim v, TenSmallest
v = [A1:B20]
With WorksheetFunction
ActiveWorkbook.Names.Add "v_", .Transpose(.Index(v, 0, 1))
End With
TenSmallest = [TRANSPOSE(TRANSPOSE(SMALL(v_,{1,2,3,4,5,6,7,8,9,10 })))]
ActiveWorkbook.Names("v_").Delete
End Sub
--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =
"Paul Stevens" wrote in message
...
Hi Charles,
Thanks for stopping me going down a blind alley.
Following your advice, to get value in 2nd dim from
minimum in 1st dim I'm now doing this
MinVal = 1 'known maximum possible value
For i = 0 To UBound(myArray)
If MinVal myArray(i, 0) Then
MinVal = myArray(i, 0)
MinIndex = i
End If
Next
Result = myArray(MinIndex, 1)
Unfortunately I need to return values in the 2nd dim'
corresponding with the 10 smallest values in the 1st dim'.
Looping with .Small seemed ideal as a starter but no
direct way to get offset values without looping the entire
array. Didn't mention this earlier as I thought possible
without looping and the principle would be the same.
Never mind, I'll work with your suggested approach.
Thanks again,
Paul
-----Original Message-----
Hi Paul,
If you already have the data in a VBA array I think the
fastest solution
would be a single pass loop: any other solution is going
to have to read
each element at least once anyway.
I suspect that the additional overhead of using a
worksheet function will
outweigh any speed gain you might get.
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com
"Paul Stevens"
wrote in message
...
In a two dimensional array I want to find the position
of
the minimum value in the first dimension and return the
value from the equivalent position in the second
dimension.
My array is a VBA variable, not a range reference, and
it's values are not in any order. Ubound is several
thousand so I don't want to loop and for other reasons
do
not want to sort. If necessary my two dim array could
be
reconstructed as two complimentary single dim arrays.
I've been messing around with various worksheet
functions
(incl .Min), but I'm fumbling!
TIA for any help,
Paul
.