|
我写了三段,分别对历史、物理、化学做处理,后面的其他表,基本都是参照这个样子写就行了。
复制粘贴然后稍微修改下参数就行了:
Dim arr
arr = Range("a2:f" & [f10000].End(3).Row) '把源数据写入数组
Rem 史的处理
If Worksheets("史").[f10000].End(3).Row >= 2 Then '先清除,为了在修改源数据后能重复统计,这个条件不可少,否则在特定条件下标题会被清掉
Worksheets("史").Range("a2:f" & Worksheets("史").[f10000].End(3).Row).ClearContents
End If
hs = 2 '写入的初始行数
For i = 1 To UBound(arr)
If arr(i, 4) = "史" Then ’d列包含“史"就写入
Worksheets("史").Cells(hs, 1) = arr(i, 1)
Worksheets("史").Cells(hs, 2) = arr(i, 2)
Worksheets("史").Cells(hs, 3) = arr(i, 3)
Worksheets("史").Cells(hs, 4) = arr(i, 4)
Worksheets("史").Cells(hs, 5) = arr(i, 5)
Worksheets("史").Cells(hs, 6) = arr(i, 6)
hs = hs + 1
End If
Next i
Rem 物的处理
If Worksheets("物").[f10000].End(3).Row >= 2 Then
Worksheets("物").Range("a2:f" & Worksheets("物").[f10000].End(3).Row).ClearContents
End If
hs = 2
For i = 1 To UBound(arr)
If arr(i, 4) = "物" Then
Worksheets("物").Cells(hs, 1) = arr(i, 1)
Worksheets("物").Cells(hs, 2) = arr(i, 2)
Worksheets("物").Cells(hs, 3) = arr(i, 3)
Worksheets("物").Cells(hs, 4) = arr(i, 4)
Worksheets("物").Cells(hs, 5) = arr(i, 5)
Worksheets("物").Cells(hs, 6) = arr(i, 6)
hs = hs + 1
End If
Next i
Rem 化的处理
If Worksheets("化").[f10000].End(3).Row >= 2 Then
Worksheets("化").Range("a2:f" & Worksheets("化").[f10000].End(3).Row).ClearContents
End If
hs = 2
For i = 1 To UBound(arr)
If (arr(i, 5) = "化") Or (arr(i, 6) = "化") Then
Worksheets("化").Cells(hs, 1) = arr(i, 1)
Worksheets("化").Cells(hs, 2) = arr(i, 2)
Worksheets("化").Cells(hs, 3) = arr(i, 3)
Worksheets("化").Cells(hs, 4) = arr(i, 4)
Worksheets("化").Cells(hs, 5) = arr(i, 5)
Worksheets("化").Cells(hs, 6) = arr(i, 6)
hs = hs + 1
End If
Next i
|
|