如果我有3 row(或 n-row) number, e.g.
row1: 12 2 5 21 10
row2: 2 4 3 5 1
row3: 5 4 3 2 2
我想每一行的每一個number都assign 一個ranking 比佢.好像第一行那樣
如果我排ascending ordering ，應該是 2，5，10，12，21
我想做的是assign
一個"4" 比 第一行的12
一個"1" 比 第一行的2
一個"2" 比 第一行的5
一個"5" 比 第一行的21
一個"3" 比 第一行的10
用一個array 去載住d "4","1","2","5","3",讓我可以make use of this array to do other operations.
how can i use vba to achieve my goal?my difficulty is i dont know how to tell vba memorize the ordering EFFICIENTLY.
can anyone help?
excel vba ordering problem
2011-04-29 2:50 am
回答 (2)
2011-04-30 1:27 am
✔ 最佳答案
Sub aaa1()Dim arr(1 To 1, 1 To 5)
Set xx = [a1:e1]
For i = 1 To 5
arr(1, i) = Application.WorksheetFunction.Rank(xx(i), xx, 1)
Next
End Sub
arr 應便是你要求的array
2011-04-29 17:27:41 補充：
Sub aaa1()
Dim arr(1 To 1, 1 To 5)
Set xx = [a1:e1]
For i = 1 To 5
arr(1, i) = Application.WorksheetFunction.Rank(xx(i), xx, 1)
Next
End Sub
arr 應便是你要求的array
SET 代表將RANGE, ARRAY, OBJECT...等改名
SET a = Range("K1")
以後引用a, 即代表Range("K1"), 是RANGE
如不加Set,
a = Range("K1")
如當時Range("K1")的值是12
a 便是代表12, 是值
2011-04-29 7:24 am
let me try try first~ i dont know excel have rank function@@"
too stupid...
2011-04-28 23:35:19 補充：
Tested~ exactly what i want~
btw, can you explain a little bit how to use "Set"?
i found that i saw this kind of syntax quite often and seems very useful.
i try to find the answer in F1 but seems not that helpful...
could you give me some example? or explain a little bit in the answer?
2011-04-28 23:35:41 補充：
so i can choose it as best answer, thz~
=]
too stupid...
2011-04-28 23:35:19 補充：
Tested~ exactly what i want~
btw, can you explain a little bit how to use "Set"?
i found that i saw this kind of syntax quite often and seems very useful.
i try to find the answer in F1 but seems not that helpful...
could you give me some example? or explain a little bit in the answer?
2011-04-28 23:35:41 補充：
so i can choose it as best answer, thz~
=]
收錄日期: 2021-04-16 12:37:43
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20110428000051KK01192