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
| 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, 1).Column
ColGap = LastColumn - ColToInsert + 1
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
End With
End Sub |
Partager