|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Sub hz1213()
Dim i, j, k, m, p, q, g1, g2, g3, g4, g5, g6, irow, irow1, icolumn, icolumn1, icolumn2
Dim tepar, ar
Dim t
t = Timer
For i = 1 To Sheets.Count
If Sheets(i).Name <> "总表" Then
irow = Sheets(i).[a65536].End(xlUp).Row
icolumn1 = Sheets(i).[iv2].End(xlToLeft).Column
icolumn2 = Sheets(i).[iv3].End(xlToLeft).Column
icolumn = WorksheetFunction.Max(icolumn1, icolumn2)
Sheets(i).Cells(7, 2 + icolumn).Resize(100, 15).ClearContents
tepar = Sheets(i).[a1].Resize(irow, icolumn)
ReDim ar(1 To irow, 1 To icolumn)
If irow = 4 Then
Sheets(i).Cells(1, 1).Resize(4, icolumn).Copy Sheets(i).Cells(4, 2 + icolumn)
End If
If irow >= 5 Then
Sheets(i).Cells(1, 1).Resize(3, icolumn).Copy Sheets(i).Cells(4, 2 + icolumn)
For k = 1 To icolumn
ar(1, k) = tepar(4, k)
Next
m = 1
For q = 5 To irow
g1 = WorksheetFunction.CountIfs(Sheets(i).Cells(q - 1, 1).Resize(1, icolumn), ">0")
g2 = WorksheetFunction.CountIfs(Sheets(i).Cells(q, 1).Resize(1, icolumn), ">0")
g3 = WorksheetFunction.CountIfs(Sheets(i).Cells(q - 1, 1).Resize(1, 7), ">0")
g4 = WorksheetFunction.CountIfs(Sheets(i).Cells(q, 1).Resize(1, 7), ">0")
g5 = WorksheetFunction.CountIfs(Sheets(i).Cells(q - 1, 1).Resize(1, 8), ">0")
g6 = WorksheetFunction.CountIfs(Sheets(i).Cells(q, 1).Resize(1, 8), ">0")
If tepar(q - 1, 3) = tepar(q, 1) And g1 = g2 And g3 = g4 And g5 = g6 Then
ar(m, 3) = tepar(q, 3)
For k = 7 To icolumn
ar(m, k) = ar(m, k) + tepar(q, k)
Next
Else
m = m + 1
For k = 1 To icolumn
ar(m, k) = tepar(q, k)
Next
End If
Next
Sheets(i).Cells(7, 2 + icolumn).Resize(m, icolumn) = ar
End If |
|