Autres Supression de formule sur des cases ciblées avec condition - Excel 2007 VBA

[otarc]

Bonjour,

J'aimerai savoir si il est possible de supprimer toutes les formules des cases contenant les mots "RechercheV", "Index", "Equiv" dans leur formule. sans enlever les valeurs des cases. Sur le classeur entier.

Une solution est-elle également possible en définissant les feuilles à choisir ?

Je vous remercie pour votre retour,

Bien cordialement

 

[pierrejean]

Bonjour otarc
A tester :
NB les noms de feuille sont a adapter (compléter si nécessaire)

Sub test()
feuilles = Array("Feuil1", "Feuil2")
For n = LBound(feuilles) To UBound(feuilles)
   For Each cel In Sheets(feuilles(n)).Cells
       If InStr(cel.Formula, "=") <> o And (InStr(cel.Formula, "RechercheV") <> 0 Or InStr(cel.Formula, "Index") = 0 Or InStr(cel.Formula, "Equiv")) = 0 Then
           cel.Value = cel.Value
       End If
   Next
Next
End Sub

 

[otarc]

Bonjour pierrejean,

J'ai testé le code, mais il tourne en boucle et ne veut pas s'arreter. J'ai remplacé Feuil1 par le nom de mon onglet c'est tout.

 

[pierrejean]

Re
C'est un peu normal vu qu'il teste toutes les cellules des feuilles
Il faudrait spécifier pour chaque feuille la zone a traiter

 

[pierrejean]

Re
Voila un exemple
les zones sont a adapter

Sub test()
feuilles = Array("Feuil1", "Feuil2")
zones = Array("A1:K100", "B5:Z200")
For n = LBound(feuilles) To UBound(feuilles)
   For Each cel In Sheets(feuilles(n)).Range(zones(n))
       If InStr(cel.Formula, "=") <> o And (InStr(cel.Formula, "RechercheV") <> 0 Or InStr(cel.Formula, "Index") = 0 Or InStr(cel.Formula, "Equiv")) = 0 Then
           cel.Value = cel.Value
       End If
   Next
Next
End Sub

 

[otarc]

Ok, cette fois ci ça s'exécute mais j'avais oublié de préciser une condition.
Actuellement le code considère si et seulement si il y a que la formule Recherche V ou Index ou Equiv. Du coup, si j'ai par exemple RechercheV associé à une autre fonction "Sierreur" par exemple, le code ne le comprendra pas, c'est bien ça ?

Si c'est le cas, il faudrait changer ça.

 

[jmfmarques]

Bonjour à tous

A otarc : si tu relis mon intervention dans ton autre discussion au sujet de tels remplacements, tu observeras que j'y évite (pour éviter des problèmes de mémoire) d'analyser toutes les cellules, ne me focalisant que sur celles contenant une formule.

cette discipline est encore plus grande dans ce que tu veux faire maintenant, car elle a également une incidence en matière de vitesse de traitement.

Voici ce que ne te propose :

Private Sub CommandButton1_Click()
  Dim sh As Worksheet, plage As Range, c As Range, k As intege, dic
  dic = Array("=*VLOOKUP*", "=*INDEX*", "=*MATCH*")
  For Each sh In ActiveWorkbook.Worksheets
    On Error Resume Next
    Set plage = sh.Cells.SpecialCells(xlCellTypeFormulas)
    If Not plage Is Nothing Then
      For Each c In plage.Cells
        For k = 0 To UBound(dic)
          If c.Formula Like dic(k) Then
            c.Value = c.Value
          End If
        Next
     Next
    End If
    On Error GoTo 0
  Next
End Sub

PS : agrémente-la (bien sur) en plus d'une inhibition de l'affichage (utilisation de screenupdating) pour accélérer encore un peu.

 

[otarc]

J'ai un soucis sur l'exécution, j'aimerai également choisir les feuilles concernées

 

[jmfmarques]

C'est un "r" qui a sauté au recopiage : lire :
k As integer,

j'aimerai également choisir les feuilles concernées

Pas de problème...
donne-nous donc leurs noms

 

[otarc]

Feuil1, Feuil2 par exemple. Par contre comme le code précédent de pierrejean, le code ne fonctionne pas j'ai l'impression si le cases contiennent plusieurs formules. (une recherchev couplée à une fonction indirect par exemple ou sierreur)

 

[otarc]

Bon en fait ça marche, j'ai rajouté les fonctions en plus et ça comprend bien la priorité je ne sais pas comment. Par exmple, j'ai une formule avec la fonction SIERREUR, elle n'est pas supprimée, mais la fonction avec la formule RECHERCHEV associé à SIERREUR et INDIRECT est supprimée.
Un autre exemple, j'ai la fonction RECHERCHEV sans être associé aux fonctions indirect qui n'est pas supprimé.

 

[fanch55]

If InStr(cel.Formula, "=") <> o And (InStr(cel.Formula, "RechercheV") <> 0 Or InStr(cel.Formula,

Salut, étonnant ..

 

[jmfmarques]

Alors voilà :

Private Sub CommandButton1_Click()

  Dim sh As Worksheet, plage As Range, c As Range, k As Integer, h As Integer, dic, feuilles

  dic = Array("=*VLOOKUP*", "=*INDEX*", "=*MATCH*")

  feuilles = Array("Feuil1", "Feuil2") '---->> mets dans cet array les feuilles concernées

  For h = 0 To UBound(feuilles)

    On Error Resume Next

    Set plage = Worksheets(feuilles(h)).Cells.SpecialCells(xlCellTypeFormulas)

    If Not plage Is Nothing Then

      For Each c In plage.Cells

        For k = 0 To UBound(dic)

          If c.Formula Like dic(k) Then

            c.Value = c.Value

          End If

        Next

     Next

    End If

    On Error GoTo 0

  Next

End Sub

Je ne comprends pas cette phrase :

Par contre comme le code précédent de pierrejean, le code ne fonctionne pas j'ai l'impression si le cases contiennent plusieurs formules. (une recherchev couplée à une fonction indirect par exemple ou sierreur)

une cellule ne peut contenir plusieurs formules !

 

[otarc]

Pardon, plusieurs fonctions je voulais dire.
J'aimerai comprendre comment le code détermine si il doit faire le choix de remplacer la cellule par la valeur ou non.

On paramètre les fonctions du coup. Moi j'ai mis ce code :

Dim sh As Worksheet, plage As Range, c As Range, k As Integer, dic
dic = Array("=*RECHERCHEV*", "=*INDEX*", "=*EQUIV*", "=*INDIRECT*", "=*SIERREUR*", "=*ESTERREUR*")
For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
Set plage = sh.Cells.SpecialCells(xlCellTypeFormulas)
If Not plage Is Nothing Then
For Each c In plage.Cells
For k = 0 To UBound(dic)
If c.Formula Like dic(k) Then
c.Value = c.Value
End If
Next
Next
End If
On Error GoTo 0
Next

End Sub

Mais quand une cellule possède plusieurs fonctions, en l'occurence, si elle possède : RECHERCHEV, INDIRECT, ESTERREUR (le tout dans une fonction SI) elle me remplace par la valeur
Si elle possède RECHERCHEV, INDIRECT elle me remplace par la valeur
Si elle possède seulement INDIRECT, elle ne remplace pas par la valeur
Si elle possède seulement RECHERCHEV, elle ne remplace pas par la valeur
Si elle possède seulement SIERREUR, elle ne remplace pas par la valeur

Ce qui me va très bien et j'ai même pas besoin de lui dire quelle feuille du coup mais ça m'intrigue.

 

[pierrejean]

Re
Essayer
If c.FormulaLocal Like dic(k) Then