- ©«¤l
- 5923
- ¥DÃD
- 13
- ºëµØ
- 1
- ¿n¤À
- 5986
- ÂI¦W
- 0
- §@·~¨t²Î
- win10
- ³nÅ骩¥»
- Office 2010
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW°ò¶©
- µù¥U®É¶¡
- 2010-5-1
- ³Ì«áµn¿ý
- 2022-1-23
|
¥»©«³Ì«á¥Ñ GBKEE ©ó 2010-6-21 07:44 ½s¿è
¦^´_ 1# wendy
¸Õ¸Õ¬Ý- Sub Ex()
- Dim Sh As Worksheet, R As Range, C As Range, S$, d(1 To 3) As Object, Ar
- Set d(1) = CreateObject("scripting.dictionary")
- Set d(2) = CreateObject("scripting.dictionary")
- Set d(3) = CreateObject("scripting.dictionary")
- Ar = Join(Application.Transpose(Application.Transpose(Sheets("1").[A3:F3])), ",")
- For Each Sh In Sheets(Array("1", "2", "3", "4"))
- With Sh
- For Each R In .Range("g3", .Range("iv3").End(xlToLeft)(1, 0))
- d(1)(R.Value) = ""
- For Each C In .Range(R(2, 1), .Cells(.Range("F" & Rows.Count).End(xlUp).Row - 1, R.Column)).SpecialCells(xlCellTypeConstants)
- If C <> "" Then
- S = R.Value & Join(Application.Transpose(Application.Transpose(.Cells(C.Row, "A").Cells.Resize(1, 6).Value)), "")
- d(2)(S) = C.Value
- S = Join(Application.Transpose(Application.Transpose(.Cells(C.Row, "A").Cells.Resize(1, 6).Value)), "")
- d(3)(S) = .Cells(C.Row, "A").Cells.Resize(1, 6).Value
- End If
- Next
- Next
- End With
- Next
- With Sheets("n¶×¾ãªºÁ`ªí")
- .Cells.Clear
- Ar = Split(Ar & "," & Join(d(1).keys, ","), ",")
- .[A1].Resize(, UBound(Ar) + 1) = Ar
- .[A2].Resize(d(3).Count, 6) = Application.Transpose(Application.Transpose(d(3).items))
- For Each R In .Range("a1").CurrentRegion.Columns
- If R.Column > 6 Then
- For Each C In R.Cells
- S = R.Cells(1) & Join(Application.Transpose(Application.Transpose(.Cells(C.Row, "A").Cells.Resize(1, 6).Value)), "")
- If d(2).Exists(S) Then C = d(2)(S)
- Next
- End If
- Next
- .Range("a1").CurrentRegion.Sort KEY1:=.[A1], KEY2:=.[F1], Header:=xlYes
-
- Set R = .Range("a1").CurrentRegion
- Set R = .Range("a1").CurrentRegion.Cells(R.Rows.Count, R.Columns.Count)
-
- .Cells(R.Row + 1, "F") = "Á`p"
- .Range(.Cells(R.Row + 1, "G"), R.Offset(1)) = "=SUM(R2C:R[-1]C)"
- .Range(.Cells(R.Row + 1, "G"), R.Offset(1)).Value = .Range(.Cells(R.Row + 1, "G"), R.Offset(1)).Value
-
- .Cells(1, R.Column + 1) = "Á`p"
- .Range(.Cells(2, R.Column + 1), R.Offset(, 1)) = "=SUM(RC7:RC[-1])"
- .Range(.Cells(2, R.Column + 1), R.Offset(, 1)).Value = .Range(.Cells(2, R.Column + 1), R.Offset(, 1)).Value
- End With
- End Sub
½Æ»s¥N½X |
|