|
本帖最后由 funfox 于 2015-1-20 23:27 编辑
- Dim rngAdr As Range, 地址, 人数
- Sub takeaway()
- Application.ScreenUpdating = False
- 算人数
- 复制and贴上
- Worksheets("成绩总表").Columns("I:K").Clear
- With Application
- .CutCopyMode = False
- .ScreenUpdating = True
- End With
- End Sub
- Private Sub 复制and贴上()
- Application.ScreenUpdating = False
- Dim rngCopd As Range, rngCopda As Range, rngSbt As Range
- Dim i人, i位, i表r As Integer, i表c As Integer
- Dim cc As String
- With rngAdr
- i人 = 0: i表r = 0: t = 0
- For Each i位 In 地址
- i人 = i人 + 1
- Set rngCopd = .Parent.Range("C" & i位, "G" & i位 + 人数(i人, 1) - 1)
- cc = .Item(i人).Value
- Set rngSbt = Worksheets(Left(cc, 3)).Cells.Find(What:=cc, MatchCase:=True)
- With rngSbt
- For i表c = 0 To 3
- With rngCopd
- Set rngCopda = Intersect(.Offset, .Parent.Range(.Item(1), .Item(28, 5)).Offset(i表c * 28))
- End With
- If rngCopda Is Nothing Then
- Else
- rngCopda.Copy
- rngSbt.Item(3, i表c * 7).PasteSpecial xlPasteValues
- End If
- Next
- With rngAdr
- On Error Resume Next
- i表r = IIf(Left(.Item(i人).Value, 3) = Left(.Item(i人 + 1).Value, 3), i表r + 1, 0)
- End With
- End With
- Next
- End With
- With Application
- .CutCopyMode = False
- .ScreenUpdating = True
- End With
- End Sub
- Private Sub 算人数()
- Application.ScreenUpdating = False
- Dim iAdr As Long
- With Worksheets("成绩总表")
- .Range(.Cells(10), .Cells(11)).Value = Array("地址", "人数")
- Set rngAdr = .Cells(65536, 1).End(xlUp)
- .Columns("A:A").Copy
- With .Columns("I:I")
- .PasteSpecial Paste:=xlPasteValues
- .RemoveDuplicates 1, 1
- With Range(.Cells(2), .Cells(65536).End(xlUp))
- .Offset(, 1).FormulaArray = "=MATCH(" & .Address & ",A:A,0)"
- .Offset(, 2).FormulaR1C1 = "=R[1]C[-1]-RC[-1]"
- With Union(.Offset(, 1), .Offset(, 2))
- .Copy
- .PasteSpecial xlPasteValues
- .Cells(.Count).Value = rngAdr(1, 2).Value
- End With
- Set rngAdr = .Offset
- 地址 = .Offset(, 1).Value
- 人数 = .Offset(, 2).Value
- End With
- End With
- End With
- With Application
- .CutCopyMode = False
- .ScreenUpdating = True
- End With
- End Sub
- '空格键消失了,真是罪过!
复制代码
|
|