|
- Option Explicit
- Sub XXL()
- Dim t, D1
- Application.Calculation = xlCalculationAutomatic '自动重算
- Dim MyrO&, ArrO, III
- MyrO = Sheets("现金流水").[A65536].End(xlUp).Row 'A列最后一个非空单元格
- ArrO = Sheets("现金流水").Range("A2:R" & MyrO) '把数据表的使用区域纳入数组
- Dim Maxrow, Rwct
- Dim Myr&, i&, Arr, Brr(1 To 14)
- Dim D As New Dictionary 'scrrun.dll后期绑定
- Dim J, R, Arr1(), II
- Set D1 = CreateObject("Scripting.Dictionary")
- Myr = Sheets("数据表").[b65536].End(xlUp).Row 'B列最后一个非空单元格
- Arr = Sheets("数据表").Range("b2:Q" & Myr) '把数据表的使用区域纳入数组
- For i = 1 To UBound(Arr) '在数组中循环
- D(Arr(i, 1)) = ""
- Next
- Application.EnableEvents = False
- If Sheets("最终效果").Range("AX2") - 1 >= D.Count Then MsgBox "已到最后一页": Sheets("最终效果").Range("AX2") = D.Count: Exit Sub
- Sheets("单户").Range("b2") = D.Keys(Sheets("最终效果").Range("AX2") - 1)
- For i = 1 To UBound(Arr)
- If Arr(i, 1) = Sheets("单户").Range("b2") Then
- Brr(1) = Arr(i, 2) '客户名称
- Brr(2) = Arr(i, 3) '借据号码
- For J = 3 To 14 '贷款余额 ~ 综合业务账号
- Brr(J) = Arr(i, J + 2)
- Next
- Sheets("单户").Range("C2").Resize(1, 14) = Brr '将结果写入 C2:P2 单元格
- R = R + 1
- ReDim Preserve Arr1(1 To 8, 1 To R) 'Arr1(1 To 8列, 1 To R行)
- If Sheets("单户").Range("G2") = "" Then
- Arr1(1, R) = Sheets("单户").Range("b5").Value '新放与上年余额订位日期
- Arr1(2, R) = "上年结转"
- Else
- Arr1(1, R) = Sheets("单户").Range("j2").Value '借款日期
- Arr1(2, R) = Sheets("单户").Range("I2").Value '借款用途
- End If
- Arr1(3, R) = Sheets("单户").Range("G2").Value '发放金额
- Arr1(5, R) = Sheets("单户").Range("M2").Value '年度结转
- Arr1(6, R) = Sheets("单户").Range("F2").Value '贷款余额
- Arr1(8, R) = Sheets("单户").Range("h2").Value '责任人
- ' ***********************************************************
- '请大师修改代码:
- '1, Sheets("单户").Range("B2").Value =SHEETS("现金流水").Range("A2"&所有使用的行).,筛选贷款帐号相同的发生额
- '2,从现金流水表中的D,E,F,G,R列中取数,分别写入单户表中B8,E8,R8,H8,G8
- If Sheets("单户").Range("B2").Value = Sheets("现金流水").Range("A2" & MyrO) Then
- For III = 1 To UBound(ArrO())
- D1(ArrO(III, 1)) = ""
- Next
- R = R + 1
- ReDim Preserve Arr1(1 To 8, 1 To R)
- Arr1(1, R) = ArrO(III, 4) 'Sheets("现金流水").Range("D2" & MyrO).Value
- Arr1(4, R) = ArrO(III, 5) 'Sheets("现金流水").Range("E2" & MyrO).Value
- Arr1(7, R) = ArrO(III, 6) 'Sheets("现金流水").Range("F2" & MyrO).Value
- Arr1(5, R) = ArrO(III, 18) 'Sheets("现金流水").Range("R2" & MyrO).Value
- If Arr1(4, R) And Arr1(7, R) > 0 Then
- Arr1(2, R) = "本息收回"
- Else
- Arr1(2, R) = "仅收利息"
- End If
- If R = 1 Then
- Arr1(6, R) = [f2].Value - Arr1(4, R)
- Else
- Arr1(6, R) = Arr1(6, R - 1) - Arr1(4, R)
- End If
- Arr1(8, R) = [H2].Value
- Else
- Exit For
- '*********************************************************************************************
- Sheets("单户").Range("b7:i100").ClearContents 'Clear
- If R <> 0 Then
- Sheets("单户").[b7].Resize(R, 8) = Application.Transpose(Arr1)
- 'With Sheets("单户").[b6].Resize(R + 1, 8)
- ' .Borders.LineStyle = 1
- ' .Font.Name = "仿宋_GB2312"
- ' .Font.Size = 9
- ' End With
- End If
- Exit For
- End If
- 'Next
- With Sheets("最终效果")
- Range("A6:AU20").ClearContents
- Range("C3,N3:AA3,AG3:AO3,AS3:AW3,AV2:AW2,AV6").ClearContents
- Maxrow = 6
- ' With Sheets("单户")
- Rwct = Sheets("单户").Range("B65536").End(xlUp).Row
- If Sheets("单户").Cells(2, 2) <> "" Then
- Cells(3, 45) = Sheets("单户").Cells(2, 2)
- Cells(3, 7) = Sheets("单户").Cells(2, 3)
- Cells(2, 48) = Sheets("单户").Cells(2, 4)
- Cells(3, 3) = Sheets("单户").Cells(2, 5)
- Cells(3, 33) = Sheets("单户").Cells(2, 14)
- Cells(3, 14) = Sheets("单户").Cells(2, 15)
- Cells(6, 48) = Sheets("单户").Cells(7, 9)
- Cells(28, 41) = "结算帐号" & Sheets("单户").Cells(2, 16)
- End If
- For i = Maxrow To Rwct
- If Sheets("单户").Cells(1 + i, 2) <> "" Then '记帐日期
- Cells(4, 1) = Right(Year(Sheets("单户").Cells(7, 2)), 2) '年2位
- Cells(i, 1) = Format(Month(Sheets("单户").Cells(1 + i, 2)), "00") '月2位
- Cells(i, 2) = Format(Day(Sheets("单户").Cells(1 + i, 2)), "00") '日2位
- Cells(i, 10) = Sheets("单户").Cells(i - 4, 12) '利率
- End If
- If Sheets("单户").Cells(1 + i, 2) <> "" Then
- Cells(6, 4) = Right(Year(Sheets("单户").Cells(2, 10)), 2) '年2位
- Cells(6, 5) = Format(Month(Sheets("单户").Cells(2, 10)), "00") '月2位
- Cells(6, 6) = Format(Day(Sheets("单户").Cells(2, 10)), "00") '日2位
- Cells(6, 7) = Right(Year(Sheets("单户").Cells(2, 11)), 2) '年2位
- Cells(6, 8) = Format(Month(Sheets("单户").Cells(2, 11)), "00") '月2位
- Cells(6, 9) = Format(Day(Sheets("单户").Cells(2, 11)), "00") '日2位
- End If
- If Sheets("单户").Cells(1 + i, 6) <> "" Then
- Cells(i, 29) = Right(Year(Sheets("单户").Cells(1 + i, 6)), 2) '年2位
- End If
- If Sheets("单户").Cells(1 + i, 6) <> "" Then
- Cells(i, 30) = Format(Month(Sheets("单户").Cells(1 + i, 6)), "00") '月2位
- End If
- If Sheets("单户").Cells(1 + i, 6) <> "" Then
- Cells(i, 31) = Format(Day(Sheets("单户").Cells(1 + i, 6)), "00") '日2位
- End If
- If Sheets("单户").Cells(1 + i, 3) <> "" Then Cells(i, 3) = Sheets("单户").Cells(1 + i, 3)
- If Sheets("单户").Cells(i + 1, 4) <> "" And Abs(Val(Sheets("单户").Cells(i + 1, 4))) >= 0 Then '借方金额分栏填充
- VaToBranch CDbl(Val(Sheets("单户").Cells(i + 1, 4))), i, 11, 19
- End If
- If Sheets("单户").Cells(i + 1, 5) <> "" And Abs(Val(Sheets("单户").Cells(i + 1, 5))) >= 0 Then '贷方金额分栏填充
- VaToBranch CDbl(Val(Sheets("单户").Cells(i + 1, 5))), i, 20, 28
- End If
- If Sheets("单户").Cells(i + 1, 7) <> "" And Abs(Val(Sheets("单户").Cells(i + 1, 7))) >= 0 Then '结存金额分栏填充
- VaToBranch CDbl(Val(Sheets("单户").Cells(i + 1, 7))), i, 32, 40
- End If
- If Sheets("单户").Cells(i + 1, 8) <> "" And Abs(Val(Sheets("单户").Cells(i + 1, 8))) >= 0 Then '利息金额分栏填充
- VaToBranch CDbl(Val(Sheets("单户").Cells(i + 1, 8))), i, 41, 47
- End If
- Next i
- End With
- Application.EnableEvents = True '打开事件
- End Sub
- Function VaToBranch(ByVal Sg#, ByVal rwC As Integer, ByVal Stcol As Integer, ByVal Edcol As Integer)
- Dim i, N, vLen As Integer
- Dim vLstr As String
- vLstr = Replace(CStr(Format(Sg, "0.00")), ".", "") '把数字格式的点替换为空
- vLen = Len(vLstr) '数字的长度
- For i = 1 To vLen '变量i的取值范围是1-数字去小数点的长度
- Cells(rwC, Edcol - vLen + i) = Mid(vLstr, i, 1) '用mid()循环取值
- Next i
- End Function '分列函数摘自论坛凭证处理130的
复制代码 我在*********和*******中加入代码后,无法运行了
请大师们帮帮,谢谢
本帖最后由 zjdh 于 2012-6-30 18:27 编辑
(, 下载次数: 24)
|
|