stillfish00

¦^´_ 1# yeh6712
¥ÎVBA®É¡A¦]¬°Rank²Ä¤G°Ñ¼Æ¦n¹³¤£¤ä´©array§Î¦¡¡A©Ò¥H¹³¤U­±³o¼ËÅo¶Û¤F¤@ÂI
ª««~Äæ¤]¥i¥H¤£¥Î¼Æ¦r

1. Sub TEST()
   
2.   Dim rngTest As Range, rngB As Range, ar, i
   
3.   
   
4.   With ActiveSheet
   
5.     Set rngTest = .Range(.[A2], .Cells(.Rows.Count, "A").End(xlUp)).Offset(, 2)
   
6.     Set rngB = .Range(.[B2], .Cells(.Rows.Count, "B").End(xlUp))
   
7.    
   
8.     Randomize Now
   
9.     For i = 1 To rngTest.Count
   
10.       rngTest(i).Value = Rnd
    
11.     Next
    
12.    
    
13.     ReDim ar(1 To rngTest.Count)
    
14.     For i = LBound(ar) To UBound(ar)
    
15.       ar(i) = Application.WorksheetFunction.Rank(rngTest(i).Value, rngTest)
    
16.       ar(i) = IIf(ar(i) > rngB.Count, "", rngB(ar(i)))
    
17.     Next
    
18.    
    
19.     rngTest.Value = Application.Transpose(ar)
    
20.   End With
    
21. End Sub

½Æ»s¥N½X

stillfish00

¦^´_ 3# yeh6712
·s¼W¤@¦æ´N¦n
    For i = LBound(ar) To UBound(ar)
        ar(i) = Application.WorksheetFunction.Rank(rngTest(i).Value, rngTest)
        rngB(ar(i)).Offset(, 2).Value = IIf(ar(i) > rngB.Count, "", rngTest(i).Offset(, -2).Value)
        ar(i) = IIf(ar(i) > rngB.Count, "", rngB(ar(i)))
    Next