|
代码放在汇总工作表模块内- Sub 汇总数据()
-
- If Len([a3]) = 0 Then MsgBox "A3单元格输入要查询的年份", vbCritical + vbOKOnly: Exit Sub
-
- On Error Resume Next
- If Not IsDate(DateSerial([a3], 1, 1)) Then MsgBox "A3单元格输入要查询的年份", vbCritical + vbOKOnly: Exit Sub
- On Error GoTo 0
- Dim cYear As String
- cYear = [a3] & "年"
- Dim arr, arrb()
- Dim iLastRow As Byte
- iLastRow = Cells(Rows.Count, 1).End(xlUp).Row - 1
- arr = Range("a4:e" & iLastRow)
- ReDim arrb(1 To UBound(arr))
- Dim i As Byte
- Dim rg As Range
- Dim arrEmpty(1 To 4)
-
- Application.ScreenUpdating = False
- With Worksheets("明细")
- On Error Resume Next
- For i = LBound(arr) To UBound(arr)
- Set rg = .Range("a:a").Find(what:=cYear & arr(i, 1), LookIn:=xlValues, lookat:=xlWhole)
- If Not rg Is Nothing Then
- arrb(i) = rg.Offset(, 1).Offset(2, 1).Resize(, 4).Value
- Else
- arrb(i) = arrEmpty
- End If
- Set rg = Nothing
- Next
- End With
- arrb = WorksheetFunction.Transpose(WorksheetFunction.Transpose(arrb))
- Range("b4").Resize(UBound(arrb), UBound(arrb, 2)) = arrb
-
- Application.ScreenUpdating = True
- MsgBox "汇总完成", vbInformation + vbOKOnly
- End Sub
复制代码 |
|