|
用数组字典也做了一个,结果和sql不同。。。。。
- Sub tt()
- Dim 总表 As Workbook, 报送表 As Workbook
- Dim Flag As Boolean
- Application.ScreenUpdating = False
- Set 总表 = Workbooks.Open(ThisWorkbook.Path & "\社保总表.xls")
- Set 报送表 = Workbooks.Open(ThisWorkbook.Path & "\社区报送表.xls")
- Set d = CreateObject("scripting.dictionary")
- arr = 总表.Sheets(1).[a2].CurrentRegion
- For i = 2 To UBound(arr)
- d(arr(i, 2)) = i
- Next
- 总表.Close False
-
- brr = 报送表.Sheets(1).[a2].CurrentRegion
- 报送表.Close False
- Application.ScreenUpdating = True
- ReDim crr(1 To 2 * UBound(brr), 1 To UBound(brr, 2))
- Dim stRng As Range '填充区域的左上角单元格
- Set stRng = [a3]
- stRng.Resize(1000, 20).Clear
- For i = 2 To UBound(brr)
- ii = Val(d(brr(i, 2)))
- If ii = 0 Then
- n = n + 1
- For k = 1 To UBound(brr, 2)
- crr(n, k) = brr(i, k)
- Next
- stRng.Offset(n - 1).Resize(1, UBound(brr, 2)).Font.Color = vbRed
- Else
- Flag = False
- For k = 1 To UBound(brr, 2)
- If arr(ii, k) <> brr(i, k) Then Flag = True: stRng.Offset(n, k - 1).Font.Color = vbRed: stRng.Offset(n + 1, k - 1).Font.Color = vbRed
- Next
- If Flag Then
- n = n + 1
- For k = 1 To UBound(brr, 2)
- crr(n, k) = brr(i, k)
- crr(n + 1, k) = arr(ii, k)
- Next
- n = n + 1
- End If
- End If
- Next
- If n > 0 Then stRng.Resize(n + 1, UBound(brr, 2)) = crr
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|