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
| Sub ExportRangeWithCondition()
' Déclaration des variables
Const FormulaMask As String = "=ISNA(MATCH(<Cel>,<Plage>,0))" ' Masque de la formule
Const ColCel As Integer = 7, ColPlage As Integer = 14 ' N° de colonne de la cellule à comparer avec la plage
Dim RngSource As Range, rngTarget As Range, rngCriteria As Range, myFormula As String
Dim adrCel As String, adrPlage As String
' Attribution des références aux objets
With ThisWorkbook
Set RngSource = .Worksheets("db2").Range("A1").CurrentRegion
Set rngTarget = .Worksheets("db2").Range("S1")
End With
With RngSource ' Zone des critères
Set rngCriteria = .Offset(columnoffset:=.Column + .Columns.Count).Resize(2, 1)
End With
With RngSource
rngTarget = .Cells(1, ColCel)
adrCel = .Cells(2, ColCel).Address(False)
adrPlage = .Range(.Cells(2, ColPlage), .Cells(.Rows.Count, ColPlage)).Address
End With
myFormula = Replace(FormulaMask, "<Cel>", adrCel)
myFormula = Replace(myFormula, "<Plage>", adrPlage)
' Place les critères dans les 2 cellules (2ème colonne à droite de rngSource et sur 2 lignes)
With rngCriteria: .Cells(1) = "formula": .Cells(2) = myFormula: End With
' Exportation
RngSource.AdvancedFilter Action:=xlFilterCopy, Criteriarange:=rngCriteria, copytorange:=rngTarget
' Fin
rngCriteria.Clear ' Efface les critères
Set rngCriteria = Nothing: Set RngSource = Nothing: Set rngTarget = Nothing
End Sub |
Partager