|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
VBA把字典改写成 纯数组方法 按列计算遗漏值
Sub 测试()
Dim ar, arr, brr()
Set d = CreateObject("scripting.dictionary")
ar = Application.Transpose(Application.Transpose(Range("k10:aq10")))
arr = Range("c11:h20")
ReDim brr(1 To UBound(arr), 1 To UBound(ar))
For i = 1 To UBound(arr)
For j = 1 To UBound(arr, 2)
d(arr(i, j)) = 0
Next j
For ii = 1 To UBound(ar)
If i = 1 Then
If Not d.exists(ar(ii)) Then d(ar(ii)) = 1
brr(i, ii) = d(ar(ii))
Else
If d(ar(ii)) <> 0 Or d(ar(ii)) = "" Then
brr(i, ii) = d(ar(ii)) + 1
d(ar(ii)) = brr(i, ii)
Else
brr(i, ii) = 0
End If
End If
Next ii
For Each k In d.keys
If d(k) = 0 Then
d.Remove k '移除为0的数据
End If
Next k
Next i
[k11].Resize(UBound(brr), UBound(brr, 2)) = brr
End Sub
|
评分
-
1
查看全部评分
-
|