|
楼主 |
发表于 2019-3-14 22:27
|
显示全部楼层
本帖最后由 ①然如故 于 2019-3-14 22:36 编辑
lsc900707你好,原来只有5个表,见图
表名称栏也只显示5个,VB编辑器中每次打开在关闭都会成倍生成sheet.
VBA代码是用在“小单位汇总”
- Option Explicit
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Target.Address <> "$B$2" And Target.Address <> "$B$3" Then Exit Sub 'And Target.Address <> "$B$4" Then Exit Sub
- Rem ?????к??к?
- Dim C As Object, d As Object, Rng As Range, ss$, n%
- Set C = CreateObject("scripting.dictionary")
- Set d = CreateObject("scripting.dictionary")
- For Each Rng In Range("B4:Q4") '???????
- n = n + 1
- If Rng.Value <> "" Then ss = Rng.Value
- C(ss & Rng.Offset(1, 0).Value) = n
- Next
- Rem ????????????
- Dim arr, i&, a(), x%, y%
- Dim key1$: key1 = Range("B2") & "*" '??λ
- Dim key2$: key2 = Range("B3") & "*" 'С??λ
- ' Dim key3$: key3 = Range("B4") & "*" '????
- n = 0
- arr = Sheets("数据源").Range("A1").CurrentRegion
- For i = 2 To UBound(arr)
- If arr(i, 3) Like key1 And arr(i, 4) Like key2 Then 'And arr(i, 6) Like key3 Then '??λ??С??λ??????
- x = C.Item(arr(i, 1) & arr(i, 11)) '????
- If Not d.Exists(arr(i, 9)) Then '???μ?
- n = n + 1: y = n
- ReDim Preserve a(1 To 16, 1 To n)
- d(arr(i, 9)) = n '???μ?
- Else
- y = d.Item(arr(i, 9)) '???μ?
- End If
- a(x, y) = a(x, y) + arr(i, 10) '????
- End If
- Next
- Rem ?????????
- Sheet3.Select
- Application.ScreenUpdating = False
- Range("A6:N500").ClearContents
- Range("A6:N500").Borders.LineStyle = xlNone
- If n > 0 Then
- With Range("A6")
- .Resize(d.Count, 1) = WorksheetFunction.Transpose(d.keys)
- .Resize(d.Count + 1, 14).Font.Bold = False
- .Offset(d.Count, 0) = "???"
- .Offset(0, 13).Resize(d.Count, 1).FormulaR1C1 = "=SUM(RC2:RC13)"
- .Offset(0, 13).Resize(d.Count, 1).Font.Bold = True
- .Resize(d.Count + 1, 14).Borders.LineStyle = xlContinuous
- End With
- With Range("B6")
- .Resize(n, 12) = WorksheetFunction.Transpose(a)
- .Offset(n, 0).Resize(1, 13).FormulaR1C1 = "=SUM(R6C:R" & d.Count + 5 & "C)"
- .Offset(n, -1).Resize(1, 14).Font.Bold = True
- End With
- End If
- Application.ScreenUpdating = True
- End Sub
- Private Sub Worksheet_SelectionChange(ByVal Target As Range) '???????????
- On Error Resume Next
- If Target.Address <> "$B$2:$K$2" And Target.Address <> "$B$3:$K$3" Then Exit Sub 'And Target.Address <> "$B$4:$I$4" Then Exit Sub
- Dim arr, d As Object, i&, x$
- Set d = CreateObject("scripting.dictionary")
- arr = Sheets("数据源").[a1].CurrentRegion
- For i = 2 To UBound(arr)
- x = arr(i, 3)
- If Not d.Exists(x) Then
- Set d(x) = CreateObject("Scripting.Dictionary")
- d(x)(arr(i, 4) & "") = arr(i, 5)
- ElseIf InStr("," & d(x)(arr(i, 4) & "") & ",", "," & arr(i, 5) & ",") = 0 Then
- d(x)(arr(i, 4) & "") = d(x)(arr(i, 4) & "") & "," & arr(i, 5)
- End If
- Next
- Sheet3.Unprotect
- With Target.Validation
- .Delete
- Select Case Target.Address
- Case "$B$2:$K$2"
- .Add xlValidateList, , , Join(d.keys, ",")
- [b3] = ""
- Case "$B$3:$K$3"
- .Add xlValidateList, , , Join(d([B2].Value).keys, ",")
- ' [b4] = ""
- ' Case "$B$4:$I$4"
- ' .Add xlValidateList, , , d([B2].Value)([b3].Value)
- End Select
- End With
- Sheet3.Protect UserInterfaceOnly:=True
- End Sub
复制代码
|
|