实例7 字典法排序一、问题的提出: A列B列是按顺序排列的全部股票代码和股票名称,C列D列和E列F列是另外按条件筛选出来的无序的数据,
代码执行前如图实例7-1所示。 图
二、代码: Private Sub CommandButton1_Click() ‘by:oobird Dim d As Object, rng, i%, j%, arr Set d = CreateObject("Scripting.Dictionary") rng = Range("a3:f" & [a65536].End(xlUp).Row) ReDim arr(1 To UBound(rng), 1 To 4) For i = 1 To UBound(rng) d(CStr(rng(i, 1))) = i Next i For j = 3 To 5 Step 2 For i = 1 To Cells(65536, j).End(xlUp).Row - 2 If d(CStr(rng(i, j))) <> "" Then arr(d(CStr(rng(i, j))), j - 2) = rng(i, j) arr(d(CStr(rng(i, j))), j - 1) = rng(i, j + 1) End If Next i Next j [c3].Resize(UBound(rng), 4) = arr End Sub 三、代码详解 1、Dim d As Object, rng, i%, j%, arr 2、Set d = CreateObject("Scripting.Dictionary") :创建字典对象d。 3、rng = Range("a3:f" & [a65536].End(xlUp).Row) :把A列到F列的单元格区域的值赋给变量rng。 4、ReDim arr(1 To UBound(rng), 1 To 4) :根据数组rng的大小重新声明动态数组变量的大小,这里是按最大数量来声明,可避免因声明得小了而导致代码出错。 5、For i = 1 To UBound(rng) 6、d(CStr(rng(i, 1))) = i 7、For j = 3 To 5 Step 2 8、For i = 1 To Cells(65536, j).End(xlUp).Row – 2 :因为C列和E列的最后一个非空单元格的位置不一样,所以用了Cells(65536, j).End(xlUp).Row在循环中分别得到这两列的最后一个非空单元格的行数,由于数组rng是从第3行开始的,为了与下面引用的rng数组对应,所以需要减去2。全句是在C列和E列中逐一循环。 9、If d(CStr(rng(i, j))) <> "" Then 10、arr(d(CStr(rng(i, j))), j - 2) = rng(i, j) 11、arr(d(CStr(rng(i, j))), j - 1) = rng(i, j + 1) 12、[c3].Resize(UBound(rng), 4) = arr
代码执行后如图实例7-2所示。 图 在17楼有实例8 继续 [此贴子已经被作者于2010-10-23 21:39:56编辑过] |
765.48 KB, 下载次数: 103
实例7
联系客服