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 |
Partager