|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
- Option Explicit
- Sub 数据提取()
- Dim arr, brr, crr, d0 As New Dictionary, d1 As New Dictionary, d2 As New Dictionary, sh1 As Worksheet, sh2 As Worksheet, h As Long, i As Long, m As String
- Set sh1 = ThisWorkbook.Worksheets("今年")
- Set sh2 = ThisWorkbook.Worksheets("前年")
- With sh1 '今年表格内容存入数组
- .AutoFilterMode = False
- h = .Cells(.Rows.Count, "A").End(xlUp).Row
- arr = .Range("A3:I" & h)
- End With
- With sh2 '前年表格内容存入数组
- .AutoFilterMode = False
- h = .Cells(.Rows.Count, "A").End(xlUp).Row
- brr = .Range("A3:I" & h)
- End With
- m = Sheet1.Range("C2").Value
- For i = 1 To UBound(arr) '今年数据提取
- If Month(VBA.CVDate(arr(i, 2))) & "月份" = m Then
- sh1.Cells(i + 2, "J") = m
- If Not d0.Exists(arr(i, 1) & arr(i, 2)) Then
- d1(arr(i, 1)) = d1(arr(i, 1)) + 1
- d0(arr(i, 1) & arr(i, 2)) = ""
- End If
- d2(arr(i, 1)) = d2(arr(i, 1)) + arr(i, 8)
- End If
- Next
- With Sheet1 '写入今年数据
- .Range("B5:D65535").ClearContents '清空历史数据
- .Range("B5").Resize(d1.Count, 1) = Application.Transpose(d1.Keys)
- .Range("C5").Resize(d1.Count, 1) = Application.Transpose(d1.Items)
- .Range("D5").Resize(d1.Count, 1) = Application.Transpose(d2.Items)
- End With
- d0.RemoveAll: d1.RemoveAll: d2.RemoveAll '清空字典
- For i = 1 To UBound(brr) '前年数据提取
- If Month(VBA.CVDate(brr(i, 2))) & "月份" = m Then
- sh2.Cells(i + 2, "J") = m
- If Not d0.Exists(brr(i, 1) & brr(i, 2)) Then
- d1(brr(i, 1)) = d1(brr(i, 1)) + 1
- d0(brr(i, 1) & brr(i, 2)) = ""
- End If
- d2(brr(i, 1)) = d2(brr(i, 1)) + brr(i, 8)
- End If
- Next
- With Sheet1 '写入前年数据
- .Range("F5:H65535").ClearContents '清空历史数据
- .Range("F5").Resize(d1.Count, 1) = Application.Transpose(d1.Keys)
- .Range("G5").Resize(d1.Count, 1) = Application.Transpose(d1.Items)
- .Range("H5").Resize(d1.Count, 1) = Application.Transpose(d2.Items)
- End With
- With Sheet1 '添加线框+字体居左
- h = .Cells(.Rows.Count, "B").End(xlUp).Row
- .Range("B5:D65535").Borders.LineStyle = xlNone
- .Range("B5:D" & h).Borders.LineStyle = 1
- .Range("B5:D" & h).HorizontalAlignment = xlLeft
- h = .Cells(.Rows.Count, "F").End(xlUp).Row
- .Range("F5:H65535").Borders.LineStyle = xlNone
- .Range("F5:H" & h).Borders.LineStyle = 1
- Range("F5:H" & h).HorizontalAlignment = xlLeft
- End With
- End Sub
复制代码 |
|