Discussion :

[mouftie]

Bonjour,
J'ai une table avec 192 colonnes qui doivent contenir des formules.
D'habitude, lorsque je supprime toutes les lignes de ma table pour la réinitialiser, je suis sensée conserver les formules.
Or depuis quelques temps, quelque soit le fichier, je me retrouve avec la perte de la moitié des formules.
Donc je veux vérifier, avant d'ajouter des données, s'il me manque des formules et si oui quel est la cellule concernée, pour lui remettre la formule.

J'ai essayé le code suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Dim PL As Range, CEL As Range, DrCol As Byte, i as byte
DrCol = Range("IV7").End(xlToLeft).Column
Set PL = Range(Cells(7, 2), Cells(7, DrCol))
For Each CEL In PL
    If CEL.Formula = "" Then GoTo linei
linei:
CEL.FormulaR1C1 = _ ma formule
Next

mon problème c'est qu'Excel considère toutes les cellules comme vide ; alors que si physiquement je fais Ctrl + flèche droite, s'il y a une formule Excel ne s'arrête pas.

Je précise que j'ai fait mes essais avec la table remplie d'une donnée

merci pour votre aide

[pijaku]

Bonjour,

L'objet Range possède une propriété HasFormula.
Il te suffit de la tester :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Sub FormuleOuPas()
Dim PL As Range, CEL As Range, DrCol As Byte
    DrCol = Range("IV7").End(xlToLeft).Column
    Set PL = Range(Cells(7, 2), Cells(7, DrCol))
    For Each CEL In PL
        If Not CEL.HasFormula Then 
            'pas de formule
        Else
            'formule
        End If
    Next
End Sub

[mouftie]

Bonjour Pijaku,
Merci pour ta réponse si rapide, mais je viens de tester, cela ne change rien, excel s'arrête sur toutes les cellules...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Sub test2()
Dim PL As Range, CEL As Range, DrCol As Byte, i As Byte
DrCol = Range("IV7").End(xlToLeft).Column
Set PL = Range(Cells(7, 2), Cells(7, DrCol))
'B7 I7 EY7
For Each CEL In PL
    i = 1
    If Not CEL.HasFormula Then GoTo linei
linei:
    MsgBox CEL.Address & " " & i
Next
End Sub

[pijaku]

Que te donne ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Sub TestFormule()
Dim PL As Range, CEL As Range, DrCol As Byte, i As Integer
    DrCol = Range("IV7").End(xlToLeft).Column
    On Error Resume Next
    Set PL = Range(Cells(7, 2), Cells(7, DrCol)).SpecialCells(xlCellTypeFormulas)
    MsgBox PL.Address
End Sub

[Philippe Tulliez]

Bonjour,
Manuellement pour sélectionner des cellules spécifiques

• Sélectionner une cellule de la feuille
• Afficher la boite de dialogue "Atteindre" (Raccourci F5)
• Cliquer sur le bouton Cellules...
• Cocher Constantes (pour sélectionner les cellules sans formule) ou Formules (pour sélectionner les cellules avec formule)
• Il y a des options en plus à décocher/cocher
• Confirmer par OK

[Menhir]

D'habitude, lorsque je supprime toutes les lignes de ma table pour la réinitialiser, je suis sensée conserver les formules.

Je ne comprends pas ce que tu expliques.
Si on supprime une ligne, on ne peut pas garder ses formules puisque, par nature, la ligne n'existe plus.
Je pense qu'il y a des éléments que tu n'expliques pas dans ta méthode et qui empêchent la compréhension.

Cependant, si ton besoin est de repérer les cellules ne contenant pas de formule, il n'y a pas besoin de VBA pour ça. Tu peux le faire avec une mise en forme conditionnelle utilisant la formule FORMULETEXTE.
Lire ça : https://support.microsoft.com/fr-fr/...e-09cda35439c8
Les cellules sans formules pourront ainsi apparaitre en rouge (ou toute autre couleur de ton choix), ce qui permettra de les identifier.

[mouftie]

@pijaku merci pour ta réponse, ton code ne renvoie rien, comme s'il n'y avait aucune cellule vide.
(@Philippe Tulliez si manuellement, je me positionne en A7 et que je fait Ctrl + flèche droite je me retrouve en C7 (B7 est vide) puis Ctrl Fleche je me retrouve en H7 (I7 est vide) puis Ctrl Fleche je me retrouve en J7 puis Ctrl Fleche je me retrouve en EX7 (EY7 est vide)...
@Menhir: Oui, je travaille en table référencée quand on vide la table avec delete, et qu'on remet une donnée en A7, on est sensée récupérer toutes les formules, cela fait 5 ans que je travaille avec ce tableau, là j'ai simplement rajouter des colonnes et cela ne fonctionnent plus... pourquoi certaines formules restent et pas toutes, je deviens folle je ne comprends pas.

Merci à tous pour votre aide

[Philippe Tulliez]

Bonjour,

(@Philippe Tulliez si manuellement, je me positionne en A7 et que je fait Ctrl + flèche droite je me retrouve en C7 (B7 est vide) puis Ctrl Fleche je me retrouve en H7 (I7 est vide) puis Ctrl Fleche je me retrouve en J7 puis Ctrl Fleche je me retrouve en EX7 (EY7 est vide)...

Je n'ai pas parlé de Ctrl+Flèche bas.
Ce que j'ai proposé c'est de sélectionner en un clic toutes les cellules d'une même feuille ayant une formule ou celles ayant des constantes mais on peut également avec la même boîte de dialogue, sélectionner les cellules vides, ayant une validation de données, mise en forme conditionnelle, etc.

C'est un excellent outil d'analyse de cellules que l'on peut bien entendu automatiser en VBA pour l'ensemble d'un classeur ou de plusieurs.

[mouftie]

@Philippe, désolée...
Si je choisis constantes, aucune cellule de ma ligne de formule n'est sélectionnée.
Si je choisis formules, dans ma ligne de formule, seules les cellules qui contiennent des formules sont sélectionnées.
Merci

[Philippe Tulliez]

Bonjour,

Si je choisis constantes, aucune cellule de ma ligne de formule n'est sélectionnée.

Si la cellule est vide, il est logique qu'elle ne soit pas sélectionnée. Une constante est une valeur quelconque dans une cellule

Si je choisis formules, dans ma ligne de formule, seules les cellules qui contiennent des formules sont sélectionnées.

Oui, cela me semble logique

Si on souhaite sélectionner les cellules vides, il faut choisir cette option dans la boîte de dialogue Atteindre

Comme je l'ai écrit, cette boite de dialogue permet de repérer très vite, dans une colonne, les cellules où la formule a disparu. Si je me réfère à votre demande

D'habitude, lorsque je supprime toutes les lignes de ma table pour la réinitialiser, je suis sensée conserver les formules.
Or depuis quelques temps, quelque soit le fichier, je me retrouve avec la perte de la moitié des formules.
Donc je veux vérifier, avant d'ajouter des données, s'il me manque des formules et si oui quel est la cellule concernée, pour lui remettre la formule.

Par exemple si en colonne H, L, M, Z, etc. j'ai une formule et qu'après avoir supprimé 4, 5 lignes, les formules n'apparaissent plus. Cette boîte de dialogue permet de les repérer.
Ce que je fais en clientèle lorsqu'on doit chercher ce genre de problème. Après sélection des cellules vides dans les colonnes concernées, je mets en couleur les cellules sélectionnées.