楼主
研究研究 |
Q:银行对账-查找两表之间差异？
每个月我都要这样一个一个数的核对到底是哪笔款记错了呢?往往对账比录账要更花时间！希望找到好的方法
A: 由于是账本，有些东西是一笔对一笔，有些是2笔对一笔（一笔是汇款，一笔是手续费）、所以我用排查法。把两边一样的都排除掉。剩下的就是对不上账的了- Sub 开始()
- Sheet2.Range("a1:d1048576").ClearContents
- Dim A As Long, D As Long, A1 As Long, X As Long, Y As Long, Y1 As Long, K As Boolean, Z As String
- 'A=SHEET1的A列 D=SHEET1的D列 A1=SHEET2的A列 X,Y,Y1=循环变量 K=是否找到 Z=SHEET1的A列字符串
- A = Range("A1048576").End(xlUp).Row
- D = Range("D1048576").End(xlUp).Row
- For X = A To 2 Step -1
- If Range("A" & X) = 0 Then Range("A" & X).Delete Shift:=xlUp
- Next X
- For X = D To 2 Step -1
- If Range("D" & X) = 0 Then Range("D" & X).Delete Shift:=xlUp
- Next X
- '以上是去掉A,D列中的0，以免以后有不必要的循环
- A = Range("A1048576").End(xlUp).Row
- For X = 2 To A
- Z = Range("A" & X).Value
- K = False
- For Y = 2 To D
- If Z = Range("d" & Y) Then
- A1 = A1 + 1
- Sheet2.Range("a" & A1) = Range("a" & X)
- Sheet2.Range("c" & A1) = Range("d" & Y)
- Range("d" & Y).Delete Shift:=xlUp
- K = True
- Exit For
- End If
- Next Y
- For Y = 2 To D
- If K = True Then Exit For
- For Y1 = Y + 1 To D
- If Z = Range("d" & Y) + Range("d" & Y1) Then
- A1 = A1 + 1
- Sheet2.Range("a" & A1) = Range("a" & X)
- Sheet2.Range("c" & A1) = Range("d" & Y)
- Sheet2.Range("d" & A1) = Range("d" & Y1)
- Range("d" & Y1).Delete Shift:=xlUp
- Range("d" & Y).Delete Shift:=xlUp
- K = True
- Exit For
- End If
- Next Y1
- Next Y
- If K = False Then Range("B" & X) = "这个数据有问题"
- Next X
- For X = A To 2 Step -1
- If Range("b" & X) = "" Then Range("A" & X).Delete Shift:=xlUp
- Next X
- Range("b2:b1048576").ClearContents
- Range("b2") = "以下数据有问题"
- End Sub
 中行人民币666.rar
|
2楼
研究研究 |
花花老师的也特别的好！
- Sub justtest()
- Dim ar1, ar2, i&, j&, k&, s$
- Application.ScreenUpdating = False
- ar1 = Range("a1:a" & Cells(Rows.Count, 1).End(3).Row).Value
- ar2 = Range("d1:d" & Cells(Rows.Count, 4).End(3).Row).Value
- Range("a:a", "d:d").Interior.Color = xlNone
- Range("e1").Interior.Color = vbYellow
- For i = 2 To UBound(ar1, 1)
- If ar1(i, 1) <> 0 Then
- For j = 2 To UBound(ar2, 1)
- If ar2(j, 1) = ar1(i, 1) Then
- ar2(j, 1) = 0
- t = True
- GoTo 100
- End If
- Next j
- For j = 2 To UBound(ar2, 1) - 1
- For k = j + 1 To UBound(ar2, 1)
- If ar2(j, 1) + ar2(k, 1) = ar1(i, 1) Then
- ar2(j, 1) = 0
- ar2(k, 1) = 0
- GoTo 100
- End If
- Next k, j
- s = s & ",a" & i
- If Len(s) > 245 Then Range(Mid(s, 2)).Interior.Color = vbYellow: s = ""
- End If
- 100
- Next i
- If Len(s) > 0 Then Range(Mid(s, 2)).Interior.Color = vbYellow: s = ""
- For i = 2 To UBound(ar2, 1)
- If ar2(i, 1) > 0 Then s = s & ",d" & i
- If Len(s) > 245 Then Range(Mid(s, 2)).Interior.Color = vbYellow: s = ""
- Next
- If Len(s) > 0 Then Range(Mid(s, 2)).Interior.Color = vbYellow: s = ""
- Application.ScreenUpdating = True
- End Sub
后续： 花花老师的快而准，但是数据分散。不利于校对
本人的虽然利用校对。但运行起来会闪动
得加上一句
Application.ScreenUpdating = False
中间是源代码
| Application.ScreenUpdating = true
|
免责声明 有感于原ExcelTip.Net留存知识的价值及部分知识具有的时间限定性因素，
经与ExcelTip.Net站长Apolloh商议并征得其同意，
现将原属ExcelTip.Net的知识帖采集资料于本站点进行展示，
供有需要的人士查询使用，也慰缅曾经的论坛时代。
所示各个帖子的原作者如对版权有异议，
可与本人沟通提出，或于本站点留言，我们会尽快处理。
在此，感谢ExcelTip.Net站长Apolloh的支持，感谢本站点所有人**绿色风（QQ:79664738)**的支持与奉献，特此鸣谢！
------本人网名**KevinChengCW（QQ:1210618015)**原ExcelTip.Net总版主之一 |