|
楼主 |
发表于 2016-1-25 15:33
|
显示全部楼层
grf1973 发表于 2016-1-25 11:24
代码已放入模块中。
回答你那个问题:
看看工作表“数据源”是否存在?或者把arr = Sheets("数据源").[a1 ...
grf1973 您好,我的意思是直接用二级下拉调用“Call 小单位数量汇总”要怎么修改。
工作表代码:
'Private Sub Worksheet_Change(ByVal Target As Range)
' If Intersect(Target, [a2:a3]) Is Nothing Then Exit Sub
' Application.EnableEvents = False
' If Target.Address = "$A$2" Then 'Exit Sub
' [a3] = ""
' [c4:J13] = ""
' ElseIf Target.Address = "$A$3" Then 'Exit Sub
'
' Call 小单位数量汇总
' End If
' Application.EnableEvents = True
'End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, [a2:a3]) Is Nothing Then Exit Sub
Set d = CreateObject("scripting.dictionary")
arr = Sheets("数据源").[a1].CurrentRegion
For i = 2 To UBound(arr)
x = arr(i, 3)
If InStr(d(x), arr(i, 4)) = 0 Then d(x) = d(x) & "," & arr(i, 4)
Next
If Target.Address = "$A$2" Then k = Join(d.keys, ",") Else k = Mid(d(Range("a2").Value), 2)
With Target.Validation
.Delete
.Add xlValidateList, , , k
End With
Call 小单位数量汇总
End Sub
上面红色代码不用的话能实现吗?
模块代码:
Sub 小单位数量汇总()
Set d = CreateObject("scripting.dictionary")
arr = Sheets("数据源").[a1].CurrentRegion
For i = 2 To UBound(arr)
x = arr(i, 3) & arr(i, 4) & arr(i, 5) & arr(i, 2) & "软肩章" & arr(i, 6) '大单位+小单位+级别+性别+软肩章号
y = arr(i, 3) & arr(i, 4) & arr(i, 5) & arr(i, 2) & "套肩章" & arr(i, 7) '大单位+小单位+级别+性别+套肩章号
d(x) = d(x) + arr(i, 1)
d(y) = d(y) + arr(i, 1)
Next
[c4:J13] = ""
arr = [a2:J13]
For i = 3 To UBound(arr) - 2
If arr(i, 1) = "" Then arr(i, 1) = arr(i - 1, 1)
For j = 3 To 7
If arr(1, j) = "" Then arr(1, j) = arr(1, j - 1)
x = [a2] & [a3] & arr(i, 1) & arr(i, 2) & arr(1, j) & arr(2, j) '大单位+小单位+级别+性别+软(套)肩章号
arr(i, j) = d(x)
arr(11, j) = arr(11, j) + arr(i, j) '第12行汇总
arr(12, 3) = arr(12, 3) + arr(i, j) '第13行总计
Next
arr(i, 8) = arr(i, 3) + arr(i, 4) + arr(i, 5)
arr(i, 9) = arr(i, 6) + arr(i, 7)
arr(i, 10) = arr(i, 8) + arr(i, 9)
arr(11, 8) = arr(11, 8) + arr(i, 8)
arr(11, 9) = arr(11, 9) + arr(i, 9)
Next
[a2:J13] = arr
End Sub
|
|