Sub 应退税额()
Dim wbm As Workbook
SelectedFile = Application.GetOpenFilename("Excel Files (*.xls; *.xlsx), *.xls; *.xlsx",,"请选择公司整体利润明细表",,False)
Set wbm = Workbooks.Open(SelectedFile)
Dim wm As Worksheet
Set wm = wbm.Worksheets("返利明细表")
Dim diccai As Object
Set diccai = CreateObject("scripting.dictionary")
Dim dic As Object
Set dic = CreateObject("scripting.dictionary")
For i =2 To wm.Range("a"& wm.Cells.Rows.Count).End(xlUp).Row
k = wm.Cells(i,3).Value
kdate = Year(k)&"年"& Month(k)&"月"
kitem = wm.Cells(i,"j")/1.22*0.22
If Not diccai.exists(kdate) Then
diccai.Add kdate, kitem
Else
diccai(kdate)= diccai(kdate)+ kitem
End If
kshoudate = CDate(Year(wm.Cells(i,6))&"/"& Month(wm.Cells(i,6))&"/1")
kdic = kdate &"-"& kshoudate
kdicitem = wm.Cells(i,"k")
If Not dic.exists(kdic) Then
dic.Add kdic, kdicitem
Else
dic(kdic)= dic(kdic)+ kdicitem
End If
Next
wbm.Close
Dim wz As Worksheet
Set wz = ThisWorkbook.Worksheets("FLASH COMMERCE S.R.L 回票情况汇总表")
For i =3 To wz.Range("a"& wz.Cells.Rows.Count).End(xlUp).Row -1
k = wz.Cells(i,2).Value
wz.Cells(i,3)= diccai(k)
endcol = wz.Range(wz.Cells(2,2), wz.Cells(2,2)).End(xlToRight).Column
For j =5 To endcol
k = wz.Cells(i,2).Value &"-"& wz.Cells(2, j)
wz.Cells(i, j)= dic(k)
Next
wz.Cells(i,4).FormulaR1C1 ="=RC[-1]-SUM(RC[1]:RC["& encol -4&"])"
Next
End Sub