|
各位老师好!
作为一个VBA初级人员,由于工作需要,我编写了一个统计客户累计的“总资产”和“总成本”的代码,但是一用起来,发现根本无法实现累加的功能。我是想对源数据表格按照要求进行筛选,再将筛选后的可视内容粘贴到sheet2表中,然后将sheet2表中同一个姓名下的多个资产计划的“总资产”和“总成本”进行累加后在另一张表上统计出来,但是我用了经典的高斯累加的公式然后进行循环,结果代码竟然只给我挑中同一个姓名下多个资产计划的某一个的“总资产”和“总成本”来粘贴过去,把其他的资产计划直接忽略了。我不知道我的代码到底哪里写错了,请各位老师不吝赐教,感激不尽。请看附件或者以下代码:
Sub 统计收益率()
Dim fa, fb As String, wor1, wor2 As Workbook
Dim i, j, g, total1, total2, count
Application.ScreenUpdating = False
iuser = Environ("username")
fa = ThisWorkbook.Path & "\" & "定投大赛源数据" & ".xlsx"
fb = ThisWorkbook.Path & "\" & "定投大赛收益率统计" & ".xlsm"
Workbooks.Open Filename:=fa
Set wor1 = ActiveWorkbook
Set wor2 = ThisWorkbook
With wor1.Sheets("Sheet1")
.Range("a1").CurrentRegion.AutoFilter Field:=2, Criteria1:="=*定投大赛*" _
, Operator:=xlAnd
.Range("a1").CurrentRegion.AutoFilter Field:=5, Criteria1:="普通定投"
.Range("a1").CurrentRegion.AutoFilter Field:=6, Criteria1:="正常"
.Range("a1").CurrentRegion.AutoFilter Field:=14, Criteria1:= _
"易方达基金零售条线"
Worksheets.Add After:=Worksheets(1)
.Range("a1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy [a1] '复制筛选后的可视单元格到新建的sheet2表
End With
With wor1.Sheets("Sheet2")
For i = 2 To .Range("a1").CurrentRegion.Rows.count
' count = 0
total1 = 0
total2 = 0
For j = 2 To wor2.Sheets("Sheet1").Range("a1").CurrentRegion.Rows.count
If wor2.Sheets("Sheet1").Range("a" & j) = .Range("m" & i) Then
total1 = total1 + .Range("i" & i)
total2 = total2 + .Range("j" & i)
count = count + 1
wor2.Sheets("Sheet1").Range("c" & j) = total1
wor2.Sheets("Sheet1").Range("d" & j) = total2
End If
Next
Next
End With
End Sub
|
|