Index of Minimum value in array
Hi Paul. Since you are looping, the same first Column array gets extracted
over and over. Perhaps you can extract that from the loop. Perhaps
something like this...
Sub Demo3()
'// Dana DeLouis, adapted
Dim v, MinLeft, ValueRight
Dim Column1
Dim i As Long
'Small test data
[A1:B20].Formula = "=RANDBETWEEN(1,100)"
[A1:B20] = [A1:B20].Value
v = [A1:B20]
With WorksheetFunction
'Keep next line out of loop...
Column1 = .Index(v, 0, 1)
For i = 1 To 10
MinLeft = .Small(Column1, i)
ValueRight = .Index(v, .Match(MinLeft, Column1, 0), 2)
Cells(i, 3).Resize(1, 2) = Array(MinLeft, ValueRight)
Next i
End With
End Sub
(I have the ATP installed for RANDBETWEEN( ) to work.)
--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =
"Paul Stevens" wrote in message
...
Dana,
Very interesting, you've come up with what I had been
struggling to find.
In my reply to Charles I mentioned my additional "smallest
10 values problem", I've adapted your code:
Sub Demo2()
'// Dana DeLouis, adapted
Dim v, MinLeft, ValueRight, i
v = [A1:B20] 'populated with =RAND(), then
'pastespecial/values back
For i = 1 To 10
With WorksheetFunction
MinLeft = .Small(.Index(v, 0, 1), i)
ValueRight = .Index(v, .Match(MinLeft, _
.Index(v, 0, 1), 0), 2)
End With
Cells(i, 3) = MinLeft
Cells(i, 4) = ValueRight
Next
'sort A1:B20 with colA and compare C1:D10 !
End Sub
Since working with Charles' single loop approach I've been
surprised to find it's faster than I expected. I'll
experiment using both methods with real life data, oh to
be spoilt with choice!
Many thanks,
Paul
-----Original Message-----
I'm not sure, but would any ideas here help you. I made
a 2-dim array
quickly from a worksheet. There might be a faster way,
but unsorted data
makes it a little harder.
Sub Demo()
'// Dana DeLouis
Dim v, MinLeft, ValueRight
v = [A1:B20]
With WorksheetFunction
MinLeft = .Min(.Index(v, 0, 1))
ValueRight = .Index(v, .Match(MinLeft, .Index(v,
0, 1), 0), 2)
End With
End Sub
--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =
"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
.
|