|
下面是chxw68大侠写的关于“得到排名的上升或下降值”的代码,比较的两列是相邻。现在我想比较不相邻的两列(sheet2中黄色的两列,即最后一列和倒数),我学着修改一下,但是我修改的运行提示“要求常数表达式”。
Sub test()
Dim r%, i%
Dim arr, brr
Dim d(2 To 3) As Object
For j = 2 To 3
Set d(j) = CreateObject("scripting.dictionary")
Next
With Worksheets("sheet1")
r = .Cells(.Rows.Count, 1).End(xlUp).Row
arr = .Range("a2:c" & r)
ReDim brr(1 To UBound(arr), 1 To 1)
For i = 1 To UBound(arr)
For j = 2 To 3
If Len(arr(i, j)) <> 0 Then
d(j)(arr(i, j)) = d(j)(arr(i, j)) + 1
End If
Next
Next
For j = 2 To 3
kk = d(j).keys
nn = 1
For k = 0 To UBound(kk)
mm = Application.Large(kk, k + 1)
ss = d(j)(mm)
d(j)(mm) = nn
nn = ss + nn
Next
Next
For i = 1 To UBound(arr)
If d(2).exists(arr(i, 2)) And d(3).exists(arr(i, 3)) Then
brr(i, 1) = d(2)(arr(i, 2)) - d(3)(arr(i, 3))
End If
Next
.Range("e2").Resize(UBound(brr), 1) = brr
End With
End Sub
|
|