|
Sub 录入数据1()
'Zjdh老师
Application.ScreenUpdating = False '关闭屏幕刷新
Dim i, x, flag
If Sheets("录入表").Cells(2, 3) <> "" And Sheets("录入表").Cells(2, 4) <> "" Then '贷款单位、借据号码不为空时
x = Sheets("数据表").Cells(65536, 2).End(3).Row '数据表末行
flag = 0 '标识清零
For i = 2 To x '逐个比较
If Sheets("录入表").Cells(2, 2) = Sheets("数据表").Cells(i, 2) Then '贷款帐号相同时
flag = 1 '标识
Exit For '退出比较
End If
Next
If flag Then '标识不为0
x = i 'X为相同贷款帐号的行号
Else
x = x + 1
End If
End If
With Sheets("录入表")
If .Cells(2, 2) = "" Then
.Cells(3, 2) = "空白表不能调用"
Else
辅助到流水1
Set SJB = Sheets("数据表")
Set XJL = Sheets("现金流水")
' x = SJB.Range("B65536").End(3).Row + 1
'如果新客户就执行X+1 如果是旧客户执行标示比较X不变
For i = 2 To 15
SJB.Cells(x, i) = .Cells(2, i) '记帐日期
Next
SJB.Cells(x, 49) = .Cells(2, 16) '科目调整日期
For i = 16 To 28
SJB.Cells(x, i) = .Cells(4, i - 13) '收回金额
Next
SJB.Cells(x, 88) = .Cells(4, 16) '客户经理
For i = 29 To 38
SJB.Cells(x, i) = .Cells(6, i - 23) '起息日期
Next
SJB.Cells(x, 89) = .Cells(6, 16) '信用等级
For i = 39 To 48
SJB.Cells(x, i) = .Cells(8, i - 33) '利息
Next
SJB.Cells(x, 90) = .Cells(8, 16) ' 授信金额
For i = 50 To 55
SJB.Cells(x, i) = .Cells(10, i - 44) '责任人
Next
SJB.Cells(x, 87) = .Cells(10, 15) '收回信贷员
SJB.Cells(x, 91) = .Cells(10, 16) '联系电话
For i = 59 To 68
SJB.Cells(x, i) = .Cells(12, i - 53) '科目调整
Next
SJB.Cells(x, 86) = .Cells(12, 3) '展期或再融资
SJB.Cells(x, 58) = .Cells(10, 2) '综合业务帐号
SJB.Cells(x, 56) = .Cells(6, 2) '身份证
SJB.Cells(x, 57) = .Cells(8, 2) '地址
请老师在此添加一个条件所有字段数据相同时不添加中止,否则执行向XJL表添加数据
y = XJL.Range("A65536").End(xlUp).Row + 1
XJL.Cells(y, 1) = .Cells(2, 2) '借款帐号
XJL.Cells(y, 2) = .Cells(2, 3) '借款单位
XJL.Cells(y, 3) = .Cells(2, 4) '借据号码
XJL.Cells(y, 4) = .Cells(2, 17) '记帐日期
XJL.Cells(y, 5) = .Cells(4, 17) '收回金额
XJL.Cells(y, 6) = .Cells(8, 17) '利息
XJL.Cells(y, 7) = .Cells(10, 15) '收回信贷员
XJL.Cells(y, 8) = .Cells(10, 8) '贷款日期
XJL.Cells(y, 9) = .Cells(10, 9) '到期日期
XJL.Cells(y, 10) = .Cells(10, 10) '利率
XJL.Cells(y, 12) = Sheets("录入表").Cells(10, 17) '凭证号码起
XJL.Cells(y, 13) = Sheets("录入表").Cells(12, 17) '凭证号码止
XJL.Cells(y, 16) = Sheets("录入表").Cells(12, 16) '新贷款科目
End If
Sheets("录入表").Range("B2:Q2,C4:Q4,F6:Q6,F8:Q8,F10:K10,O10:Q10,F12:Q12,B6,B8,B10,C11,C12") = ""
.Cells(3, 2) = "录入成功!!"
End With
起息日期741
起息日期811
Application.ScreenUpdating = True '打开屏幕刷新
Application.Calculation = xlCalculationAutomatic '自动重算
End Sub
本帖最后由 zjdh 于 2011-8-20 08:19 编辑
- Sub 录入数据1()
- 'Zjdh老师
- Application.ScreenUpdating = False '关闭屏幕刷新
- Dim i, x, flag
- If Sheets("录入表").Cells(2, 3) <> "" And Sheets("录入表").Cells(2, 4) <> "" Then '贷款单位、借据号码不为空时
- x = Sheets("数据表").Cells(65536, 2).End(3).Row '数据表末行
- flag = 0 '标识清零
- For i = 2 To x '逐个比较
- If Sheets("录入表").Cells(2, 2) = Sheets("数据表").Cells(i, 2) Then '贷款帐号相同时
- flag = 1 '标识
- Exit For '退出比较
- End If
- Next
- If flag Then '标识不为0
- x = i 'X为相同贷款帐号的行号
- Else
- x = x + 1
- End If
- End If
- With Sheets("录入表")
- If .Cells(2, 2) = "" Then
- .Cells(3, 2) = "空白表不能调用"
- Else
- 辅助到流水1
- Set SJB = Sheets("数据表")
- Set XJL = Sheets("现金流水")
- ' x = SJB.Range("B65536").End(3).Row + 1
- '如果新客户就执行X+1 如果是旧客户执行标示比较X不变
- For i = 2 To 15
- SJB.Cells(x, i) = .Cells(2, i) '记帐日期
- Next
- SJB.Cells(x, 49) = .Cells(2, 16) '科目调整日期
- For i = 16 To 28
- SJB.Cells(x, i) = .Cells(4, i - 13) '收回金额
- Next
- SJB.Cells(x, 88) = .Cells(4, 16) '客户经理
- For i = 29 To 38
- SJB.Cells(x, i) = .Cells(6, i - 23) '起息日期
- Next
- SJB.Cells(x, 89) = .Cells(6, 16) '信用等级
- For i = 39 To 48
- SJB.Cells(x, i) = .Cells(8, i - 33) '利息
- Next
- SJB.Cells(x, 90) = .Cells(8, 16) ' 授信金额
- For i = 50 To 55
- SJB.Cells(x, i) = .Cells(10, i - 44) '责任人
- Next
- SJB.Cells(x, 87) = .Cells(10, 15) '收回信贷员
- SJB.Cells(x, 91) = .Cells(10, 16) '联系电话
- For i = 59 To 68
- SJB.Cells(x, i) = .Cells(12, i - 53) '科目调整
- Next
- SJB.Cells(x, 86) = .Cells(12, 3) '展期或再融资
- SJB.Cells(x, 58) = .Cells(10, 2) '综合业务帐号
- SJB.Cells(x, 56) = .Cells(6, 2) '身份证
- SJB.Cells(x, 57) = .Cells(8, 2) '地址
- y = XJL.Range("A65536").End(xlUp).Row + 1
- '***判断****
- For i = 2 To y - 1
- If XJL.Cells(i, 1) = .Cells(2, 2) Then
- s = 1
- If XJL.Cells(i, 2) = .Cells(2, 3) Then s = s + 1
- If XJL.Cells(i, 3) = .Cells(2, 4) Then s = s + 1
- If XJL.Cells(i, 4) = .Cells(2, 17) Then s = s + 1
- If XJL.Cells(i, 5) = .Cells(4, 17) Then s = s + 1
- If XJL.Cells(i, 6) = .Cells(8, 17) Then s = s + 1
- If XJL.Cells(i, 7) = .Cells(10, 15) Then s = s + 1
- If XJL.Cells(i, 8) = .Cells(10, 8) Then s = s + 1
- If XJL.Cells(i, 9) = .Cells(10, 9) Then s = s + 1
- If XJL.Cells(i, 10) = .Cells(10, 10) Then s = s + 1
- If XJL.Cells(i, 12) = .Cells(10, 17) Then s = s + 1
- If XJL.Cells(i, 13) = .Cells(12, 17) Then s = s + 1
- If XJL.Cells(i, 16) = .Cells(12, 16) Then s = s + 1
- End If
- If s = 13 Then Exit For
- Next
- '******
- If s <> 13 Then
- XJL.Cells(y, 1) = .Cells(2, 2) '借款帐号
- XJL.Cells(y, 2) = .Cells(2, 3) '借款单位
- XJL.Cells(y, 3) = .Cells(2, 4) '借据号码
- XJL.Cells(y, 4) = .Cells(2, 17) '记帐日期
- XJL.Cells(y, 5) = .Cells(4, 17) '收回金额
- XJL.Cells(y, 6) = .Cells(8, 17) '利息
- XJL.Cells(y, 7) = .Cells(10, 15) '收回信贷员
- XJL.Cells(y, 8) = .Cells(10, 8) '贷款日期
- XJL.Cells(y, 9) = .Cells(10, 9) '到期日期
- XJL.Cells(y, 10) = .Cells(10, 10) '利率
- XJL.Cells(y, 12) = .Cells(10, 17) '凭证号码起
- XJL.Cells(y, 13) = .Cells(12, 17) '凭证号码止
- XJL.Cells(y, 16) = .Cells(12, 16) '新贷款科目
- End If
- End If
- Sheets("录入表").Range("B2:Q2,C4:Q4,F6:Q6,F8:Q8,F10:K10,O10:Q10,F12:Q12,B6,B8,B10,C11,C12") = ""
- .Cells(3, 2) = "录入成功!!"
- End With
- 起息日期741
- 起息日期811
- Application.ScreenUpdating = True '打开屏幕刷新
- End Sub
复制代码
|
|