[已解决]请教月度工资汇总。

入伍小兵

想通过宏汇总E列单位代码（对应1至12表中B列）的汇总单位的月度工资（对应1至12表的AA列）。（现在为了方便，只用3个月，今后月份工工资表格会随月份增加而增加，最多12个月）

见附件

aowkEm2X.rar (45.34 KB, 下载次数: 23)

2010-3-22 20:51 上传

点击文件名下载附件
请教月度工资汇总。

最佳答案

月排行榜 / 总排行榜

gdgzlyh

2010-3-24 01:27

Sub temp()
Dim ARng As Range
Dim BRng As Range
Dim AFR As Range
Dim BFR As Long
Dim SHName As String
Dim ARNum As Long
Dim BRNum As Long
Dim ACNum As Byte

Set ARng = Application.InputBox(prompt:="请输入需要汇总的单元格", Title:="工资汇总", Type:=8)
ARNum = ARng.Rows.Count
Set BRng = Application.InputBox(prompt:="请输入需要汇总的区域", Title:="工资汇总", Type:=8)
SHName = BRng.Worksheet.Name
BRNum = BRng.Rows.Count
ACNum = CLng(SHName)
With Worksheets("单位工资统计")
For Each AFR In ARng
For BFR = 10 To (BRNum + 10)
If AFR.Value = Worksheets(SHName).Cells(BFR, 2).Value Then
.Cells(AFR.Row, AFR.Column + ACNum).Value = .Cells(AFR.Row, AFR.Column + ACNum).Value + Worksheets(SHName).Cells(BFR, 27).Value
End If
Next BFR
Next AFR
End With
Set ARng = Nothing
Set BRng = Nothing
End Sub

跳转到最佳答案楼层

fjmxwrs

个人感觉用透视表可能更方便，可是我还不会

入伍小兵

QUOTE:

以下是引用fjmxwrs在2010-3-22 21:12:00的发言：
个人感觉用透视表可能更方便，可是我还不会

现在为了方便，只用3个月，今后月份工工资表格会随月份增加而增加，最多12个月。

心随云飞

学习。。。。。。。。。

[此贴子已经被作者于2010-3-23 20:36:44编辑过]

入伍小兵

QUOTE:

以下是引用包您满意在2010-3-23 20:42:00的发言：
其实不用用宏的。。用函数也可以操作的而且也不会很复杂。。。

假如不用宏，每月有5000人，统计12个月用函数统计速度是不是很慢？

gdgzlyh

Sub temp()
Dim ARng As Range
Dim BRng As Range
Dim AFR As Range
Dim BFR As Long
Dim SHName As String
Dim ARNum As Long
Dim BRNum As Long
Dim ACNum As Byte

Set ARng = Application.InputBox(prompt:="请输入需要汇总的单元格", Title:="工资汇总", Type:=8)
ARNum = ARng.Rows.Count
Set BRng = Application.InputBox(prompt:="请输入需要汇总的区域", Title:="工资汇总", Type:=8)
SHName = BRng.Worksheet.Name
BRNum = BRng.Rows.Count
ACNum = CLng(SHName)
With Worksheets("单位工资统计")
For Each AFR In ARng
For BFR = 10 To (BRNum + 10)
If AFR.Value = Worksheets(SHName).Cells(BFR, 2).Value Then
.Cells(AFR.Row, AFR.Column + ACNum).Value = .Cells(AFR.Row, AFR.Column + ACNum).Value + Worksheets(SHName).Cells(BFR, 27).Value
End If
Next BFR
Next AFR
End With
Set ARng = Nothing
Set BRng = Nothing
End Sub

入伍小兵

QUOTE:

以下是引用gdgzlyh在2010-3-24 1:27:00的发言：

Sub temp()
Dim ARng As Range
Dim BRng As Range
Dim AFR As Range
Dim BFR As Long
Dim SHName As String
Dim ARNum As Long
Dim BRNum As Long
Dim ACNum As Byte

Set ARng = Application.InputBox(prompt:="请输入需要汇总的单元格", Title:="工资汇总", Type:=8)
ARNum = ARng.Rows.Count
Set BRng = Application.InputBox(prompt:="请输入需要汇总的区域", Title:="工资汇总", Type:=8)
SHName = BRng.Worksheet.Name
BRNum = BRng.Rows.Count
ACNum = CLng(SHName)
With Worksheets("单位工资统计")
For Each AFR In ARng
For BFR = 10 To (BRNum + 10)
If AFR.Value = Worksheets(SHName).Cells(BFR, 2).Value Then
.Cells(AFR.Row, AFR.Column + ACNum).Value = .Cells(AFR.Row, AFR.Column + ACNum).Value + Worksheets(SHName).Cells(BFR, 27).Value
End If
Next BFR
Next AFR
End With
Set ARng = Nothing
Set BRng = Nothing
End Sub

测试类型不匹配。

gdgzlyh

[em06]

入伍小兵

是我操作没对，可以实现。谢谢。