1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110
|
Option Explicit
Sub Tst()
Comparaison2Feuilles Sheets("Feuil1"), Sheets("Feuil2")
End Sub
Private Sub Comparaison2Feuilles(ByVal Sh1 As Worksheet, ByVal Sh2 As Worksheet)
Dim r As Long, c As Long
Dim r1 As Long, r2 As Long
Dim c1 As Long, c2 As Long
Dim RMax As Long, CMax As Long
Dim Formule1 As String, Formule2 As String
Dim Ws As Worksheet
Dim RapportSim As Worksheet
Dim RapportDiff1 As Worksheet
Dim RapportDiff2 As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each Ws In Worksheets
On Error Resume Next
If Ws.Name = "Diff1" Then Ws.Delete
If Ws.Name = "Diff2" Then Ws.Delete
If Ws.Name = "Sim" Then Ws.Delete
On Error GoTo 0
Next Ws
Application.DisplayAlerts = True
Set RapportSim = ThisWorkbook.Worksheets.Add
Set RapportDiff1 = ThisWorkbook.Worksheets.Add
Set RapportDiff2 = ThisWorkbook.Worksheets.Add
RapportSim.Name = "Sim"
RapportDiff1.Name = "Diff1"
RapportDiff2.Name = "Diff2"
With Sh1.UsedRange
r1 = .Rows.Count
c1 = .Columns.Count
End With
With Sh2.UsedRange
r2 = .Rows.Count
c2 = .Columns.Count
End With
RMax = r1: CMax = c1
If RMax < r2 Then RMax = r2
If CMax < c2 Then CMax = c2
For c = 1 To CMax
For r = 1 To RMax
Formule1 = "": Formule2 = ""
Formule1 = Sh1.Cells(r, c)
Formule2 = Sh2.Cells(r, c)
If Formule1 <> Formule2 Then
If IsNumeric(Formule1) Then
RapportDiff1.Cells(r, c) = CDbl(Formule1)
Else
RapportDiff1.Cells(r, c) = Formule1
End If
If IsNumeric(Formule2) Then
RapportDiff2.Cells(r, c) = CDbl(Formule2)
Else
RapportDiff2.Cells(r, c) = Formule2
End If
Else
If IsNumeric(Formule1) Then
RapportSim.Cells(r, c) = CDbl(Formule1)
Else
RapportSim.Cells(r, c) = Formule1
End If
End If
Next r
Next c
RapportSim.Cells.Columns.AutoFit
RapportDiff1.Cells.Columns.AutoFit
RapportDiff2.Cells.Columns.AutoFit
RapportSim.Move After:=Sheets(5)
RapportDiff1.Move After:=Sheets(5)
RapportDiff2.Move After:=Sheets(5)
DetruireLignesVides RapportSim
DetruireLignesVides RapportDiff1
DetruireLignesVides RapportDiff2
Set RapportSim = Nothing
Set RapportDiff1 = Nothing
Set RapportDiff2 = Nothing
Application.ScreenUpdating = True
End Sub
Sub DetruireLignesVides(ByVal Ws As Worksheet)
Dim i As Long, iMax As Long
Ws.Activate
iMax = ActiveSheet.UsedRange.Rows.Count
For i = iMax To 1 Step -1
If Application.CountA(Ws.Cells(i, 1).EntireRow) = 0 Then
Ws.Cells(i, 1).EntireRow.Delete
End If
Next i
End Sub |
Partager