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
| Sub Compare_data()
Dim LRDB1 As Long
Dim StartRange As Range, ColToInsert As Byte, LastColumn As Byte, ColGap As Byte, FirstRow As Integer, LastRow As Long
Application.ScreenUpdating = False
'Concaténer les 7 dimensions analytiques en colonne A
With Sheets("Data Base")
LRDB1 = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("A1").EntireColumn.Insert
With .Range("A2:A" & LRDB1)
.Formula = "=CONCATENATE(RC[1],RC[2],RC[3],RC[4],RC[5],RC[6],RC[7])"
.Value = .Value
End With
End With
'Rechercher la 1ère des 7 colonnes contenant les données de dimension analytique et définir la dernière colonne
With Sheets("Data to check")
Set StartRange = .Cells.Find("Responsable", LookIn:=xlValues)
ColToInsert = StartRange.Column
LastColumn = StartRange.End(xlToRight).Offset(0, 2).Column
ColGap = LastColumn - ColToInsert
FirstRow = StartRange.Row + 1
LastRow = StartRange.End(xlDown).Row
StartRange.EntireColumn.Insert
With .Range(.Cells(FirstRow, ColToInsert), .Cells(LastRow, ColToInsert))
.Formula = "=CONCATENATE(RC[1],RC[2],RC[3],RC[4],RC[5],RC[6],RC[7])"
.Value = .Value
End With
With .Range(.Cells(FirstRow, LastColumn), .Cells(LastRow, LastColumn))
.Formula = "=IF(ISERROR(VLOOKUP(RC[" & -ColGap & "],'Data Base'!C1,1,0)),""A vérifier"",""OK"")"
.Value = .Value
End With
StartRange.Offset(0, -1).EntireColumn.Delete
.Range(.Cells(1, LastColumn - 1)) = "Dimensions à vérifier"
With .Range(.Cells(1, LastColumn - 1), .Cells(LastRow, LastColumn - 1))
.AutoFilter Field = 1, Criteria1:="A vérifier"
End With
End With
End Sub |
