|
本帖最后由 hyptony 于 2023-2-21 18:43 编辑
此前在论坛上找到了一段代码,正在研究如何使用。请教下各位大神,下面这段代码要想实现只对EXCEL 筛选出来的数据进行计算和统计的话,该如何修改,谢谢了
这个表格的作用是通过VBA筛选不同的人名自动求和对应的工日数(借鉴了论坛另外朋友的模板)。由于是不同的人分别在不同的项目上工作,我想在此基础上通过手动进行项目的筛选,筛选后VBA再自动按人名对应的工日数汇总求和。A列是项目名称,具体请见附件。
补充下:只对A列的数据进行筛选
Sub aaa()
Dim ar1, ar2
Dim i As Integer, j As Integer, Maxrow As Long, maxrow2 As Long
Maxrow = Sheets(1).[c65536].End(3).Row
maxrow2 = Sheets(2).[c65536].End(3).Row
Sheets(2).Range("E2:I" & maxrow2).ClearContents
ar1 = Sheets(1).Range("A1:I" & Maxrow)
ar2 = Sheets(2).Range("D2:I" & maxrow2)
For i = 1 To UBound(ar2)
For j = 2 To UBound(ar1)
If ar1(j, 4) = ar2(i, 1) Then
ar2(i, 2) = ar2(i, 2) + ar1(j, 5)
ar2(i, 4) = ar2(i, 4) + ar1(j, 7)
End If
Next j
ar2(i, 6) = ar2(i, 2) - ar2(i, 4)
Next i
Sheets(2).Range("D2:I" & maxrow2) = ar2
End Sub
Sub Multi_aa()
Dim ar1, ar2, Rownum, ar3
Dim i As Integer, j As Integer, Maxrow As Long, maxrow2 As Long
Dim dic As Object
Set dic = CreateObject("scripting.dictionary")
Maxrow = Sheets(1).[c65536].End(3).Row
Sheets(2).Range("A2:I65536").ClearContents
ar1 = Sheets(1).Range("A1:I" & Maxrow)
For i = 1 To UBound(ar1)
If ar1(i, 4) <> "" And ar1(i, 4) <> "负责人" Then dic(ar1(i, 4)) = ""
Next i
maxrow2 = dic.Count
ReDim Rownum(1 To maxrow2)
For i = 1 To maxrow2
Rownum(i) = i
Next i
Sheets(2).Range("D2").Resize(dic.Count, 1) = Application.WorksheetFunction.Transpose(dic.keys)
Sheets(2).Range("C2").Resize(dic.Count, 1) = Application.WorksheetFunction.Transpose(Rownum)
ar3 = dic.keys
ar2 = Sheets(2).Range("E2:I" & maxrow2 + 1)
For i = 1 To UBound(ar2)
For j = 2 To UBound(ar1)
If ar1(j, 4) = ar3(i - 1) Then
ar2(i, 1) = ar2(i, 1) + ar1(j, 5)
ar2(i, 3) = ar2(i, 3) + ar1(j, 7)
End If
Next j
ar2(i, 5) = ar2(i, 1) - ar2(i, 3)
Next i
Sheets(2).Range("e2:I" & maxrow2 + 1) = ar2
End Sub
Sub delete()
Sheets(2).Range("C2:I65536").ClearContents
End Sub
|
|