XL 2010 Macro VBA Excel (Doublon,SansAccents,Maj,Min,Nompropre,Espaces superflus)

Sub Concat()
    Dim conc(), n&, i&, bool&, x1$, x2$
    With ActiveSheet
        'comme on commence le tableau en ligne 1 pas la peine de se casser la tete
        n = ActiveSheet.UsedRange.Rows.Count
        ReDim conc(1 To n)
        For i = 2 To n
            x1 = Trim(UCase(Replace(Replace(Replace(.Cells(i, 7).Value, "-", ""), "'", ""), " ", "")))
            x2 = Trim(UCase(Replace(Replace(Replace(.Cells(i, 8).Value, "-", ""), "'", ""), " ", "")))
            bool = Abs(x1 <> "" And x2 <> "")
            conc(i - 1) = (x1 & " " & x2) & "/EXCEL"    'modifier le nom de l'entité avant de lancer
        Next i
        Application.EnableEvents = False
        .Cells(2, 3).Resize(UBound(conc) + 1, 1).Value = Application.Transpose(conc)
        Application.EnableEvents = True
    End With
End Sub

Sub Concat()
    Dim conc(), n&, i&, bool&, x1$, x2$
    With ActiveSheet
        'comme on commence le tableau en ligne 1 pas la peine de se casser la tete
        n = ActiveSheet.UsedRange.Rows.Count
        ReDim conc(1 To n)
        For i = 2 To n
            x1 = Trim(UCase(Replace(Replace(Replace(.Cells(i, 7).Value, "-", ""), "'", ""), " ", "")))
            x2 = Trim(UCase(Replace(Replace(Replace(.Cells(i, 8).Value, "-", ""), "'", ""), " ", "")))
             trim(conc(i - 1) = (x1 & " " & x2) & "/EXCEL" )   'modifier le nom de l'entité avant de lancer
        Next i
        Application.EnableEvents = False
        .Cells(2, 3).Resize(UBound(conc) + 1, 1).Value = Application.Transpose(conc)
        Application.EnableEvents = True
    End With
End Sub

Function ChangeAllCellpropertiesInRange(ByRef RnG As Range, prop As String)
    Dim R As Variant, Addr

    With RnG
        Addr = "'" & .Parent.Name & "'!" & .Address
        Select Case UCase(prop)


            'formule non matricielles
        Case "LOWER", "UPPER", "PROPER", "APPTRIM":
            prop = Replace(UCase(prop), "APPTRIM", "TRIM")
            R = Evaluate("IF(ISTEXT(" & Addr & ")," & UCase(prop) & "(" & Addr & "),REPT(" & Addr & ",1))")

            'formules matricielle
        Case "LTRIM": R = Evaluate("IF(ISTEXT(" & Addr & "),MID(" & Addr & ",FIND(MID(TRIM(" & Addr & "),1,2)," & Addr & ",1),LEN(" & Addr & ")),REPT(" & Addr & ",1))")

             'nouvelle formule
        Case "RTRIM": R = Evaluate("IF(ISTEXT(" & Addr & "),LEFT(" & Addr & ",FIND(""§"",SUBSTITUTE(" & Addr & ",RIGHT(TRIM(" & Addr & "),1),""§"",LEN(" & Addr & ")-LEN(SUBSTITUTE(" & Addr & ",RIGHT(TRIM(" & Addr & "),1),""""))),1))," & Addr & ")")

        Case "TRIM": .Value = Evaluate("IF(ISTEXT(" & Addr & "),MID(" & Addr & ",FIND(MID(TRIM(" & Addr & "),1,2)," & Addr & ",1),LEN(" & Addr & ")),REPT(" & Addr & ",1))")
            R = Evaluate("IF(ISTEXT(" & Addr & "),MID(" & Addr & ",1,FIND(TRIM(RIGHT(SUBSTITUTE(TRIM(" & Addr & "), "" "", REPT("" "", 100)), 100))," & Addr & ",1)+LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(" & Addr & "), "" "", REPT("" "", 100)), 100)))-1),REPT(" & Addr & ",1))")

        End Select
    End With

    ChangeAllCellpropertiesInRange = R
End Function


Sub testx()
    Dim DL, RnG As Range
         Set RnG = Selection
        'RnG.Parent.Activate
        RnG.Value = ChangeAllCellpropertiesInRange(RnG, "lower")    'majuscule ou minuscule l'argument de propertie
    
End Sub