Formule excel qui determine si une celulle contient une formule

[ConsultantJP]

Bonjour à toutes et à tous ! (et bonnes vacances aux chanceux )

Je cherche une solution en vain qui me permettrait de savoir si une cellule contient ou non une formule.

J'ai déja trouvé une solution en VBA, (hasformula) qui marche très bien, mais vu que cette fucntion est déployé sur un nombre énorme de ligne mon fichier est très lent.

Je pense que la lenteur vient du fait qu'il s'agit d'une fonction personnalisée, mon objectif est donc d'essayer de trouver une solution avec des formules existantes de manière native.

Cela fait plusieurs jours que je teste et cherche sur le net mais je n'ai rien trouvé du tout !

Avez vous une idée ???
Merci par avance,
JP

 

[KenDev]

Re : Formule excel qui determine si une celulle contient une formule

Bonjour JP,

A tester sur un grand nombre de cellules, je ne me rends pas bien compte de la rapidité sur une poignée de cellules. A adapter pour un traitement en série :

Option Explicit

Dim b As Boolean

Sub rrr()
    Call TestFormule(Cells(7, 4)) 'tester cellule D7
    MsgBox b
End Sub

Sub TestFormule(c As Range)
Dim Rg As Range, d As Range
    b = False
    Set Rg = Range("A1").SpecialCells(xlCellTypeFormulas, 23)
    For Each d In Rg
        If Not Intersect(Rg, d) Is Nothing And c.Address = d.Address Then
            b = True
            Exit For
        End If
    Next d
End Sub

 

[ConsultantJP]

Re : Formule excel qui determine si une celulle contient une formule

Merci pour ta réponse mais en faite , en gros dans la cellule A1 , je marque = estformule(a2) et ça me répond oui ou non et en fonction du résultat , je lance ou non une autre formule.

Je dois avoir sur le meme doc environ 5000 fois cette formule et je voulais voir si toutefois une formule indirect, equiv décaler, type ou autre aurait pu me permettre de déterminer si c une formule ou si le premier caractère est = par exemple.

Merci

 

[brunosc]

Re : Formule excel qui determine si une celulle contient une formule

Bonjour, voici une macro qui met en couleur les cellules qui contiennent des macros :
Sub emplacementFormules()

Dim Cell As Range

For Each Cell In Feuil1.usedRange.Cells

If Cell.hasFormula Then Cell.Interior.colorIndex = 4

Next Cell

End Sub

Teste et dit nous si cela te va.
Amicalement

 

[Pierrot93]

Re : Formule excel qui determine si une celulle contient une formule

Bonjour consultant, KD

Merci pour ta réponse mais en faite , en gros dans la cellule A1 , je marque = estformule(a2) et ça me répond oui ou non et en fonction du résultat , je lance ou non une autre formule.

euh... pas tout compris moi... un tout petit exemple bien concret, serait sans doute le bienvenu...

bonne soirée
@+

Edition : bonsoir Bruno

 

[ConsultantJP]

Re : Formule excel qui determine si une celulle contient une formule

voici un exemple , en esperant avoir été plus clair !!

merci

 

[Pierrot93]

Re : Formule excel qui determine si une celulle contient une formule

Bonjour,

as tu essayé de mettre le calcul sur "ordre", et ensuite soit tu lances manuellement le calcul (F9), tout le classeur sera recalculé, ou bien le déclencher sur une plage donnée de cellules en fonction des modif apporter sur ta feuille de calcul et cegrace à une procédure événementielle...

Private Sub Worksheet_Change(ByVal Target As Range)
Rows(Target.Row).Calculate 'plage à déterminer fonction de la cellule modifiée
End Sub

 

[KenDev]

Re : Formule excel qui determine si une celulle contient une formule

Bonjour JP, Pierrot, Brunosc,

Pour l'exemple j'ai :

Sub rrr()
    Call TestFormule(Range(Cells(2, 2), Cells(2, 13)))
End Sub
Sub TestFormule(c As Range)
Dim Rg As Range, Rh As Range, d As Range
   
    Set Rg = Range("A1").SpecialCells(xlCellTypeFormulas, 23)
    Set Rh = Intersect(Rg, c)
    For Each d In c
        If Not Intersect(Rh, d) Is Nothing Then
            d.Offset(1, 0) = 2 * d
        Else
            d.Offset(1, 0) = 20 * d
        End If
    Next d
End Sub

J'ai testé sur 10000 lignes dont la moitié (réparties aléatoirement) contiennent une formule et l'autre moitié une constante. Résultat 10 secondes sur un netbook très fatigué..

Les codes pour le test :

générer 10000 lignes avec ou sans formule :

Sub ghgh()
Dim i&
    Application.ScreenUpdating = False
    Randomize
    Cells(1, 1) = 100
    For i = 2 To 10000
        If Rnd < 0.5 Then
            Cells(i, 1).FormulaR1C1 = "=10*R[-1]C"
        Else
            Cells(i, 1) = 100
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

.


les opérations :

Sub rrrbis()
    Call TestFormulebis(Range(Cells(1, 1), Cells(10000, 1)))
End Sub
Sub TestFormulebis(c As Range)
Dim Rg As Range, Rh As Range, d As Range
   Application.ScreenUpdating = False
    Set Rg = Range("A1").SpecialCells(xlCellTypeFormulas, 23)
    Set Rh = Intersect(Rg, c)
    For Each d In c
        If Not Intersect(Rh, d) Is Nothing Then
            d.Offset(0, 1) = 2 * d
        Else
            d.Offset(0, 1) = 20 * d
        End If
    Next d
    Application.ScreenUpdating = True
End Sub

Cordialement

KD

 

[ConsultantJP]

Re : Formule excel qui determine si une celulle contient une formule

Déja merci à tous, pierrot alors je vais ta préconisation mais à vrai dire je suis pas un fan du cacul manuel car les utilisateurs utilisent toujours plein de fichiers en même temps et à vrai dire je ne sais pas faire calcul manuel que sur un fichier.

Et par rapport à ta solution Kendev qui me parait vraiment très bien mais pas forcement pour mon cas car mes calculs sont vraiment très très complexes ( 5 ou 6 lignes pour des formules excel c pas mal ) donc en vba ça me parait compliqué, et en autre point parrallement j'ai des MCF avec le estformule qui indiquent visuellement lorsque c des formules, donc au global je ne peux pas tester cette solution je pense.

Donc pour vous aussi , aucune solution Excel avec des formules natives ?? dommage ! meme si au fond de moi je me dis que doit bien y avoir un truc quand meme....

 

[Staple1600]

Re : Formule excel qui determine si une celulle contient une formule

Bonsoir à tous

Je pense que la lenteur vient du fait qu'il s'agit d'une fonction personnalisée, mon objectif est donc d'essayer de trouver une solution avec des formules existantes de manière native.

C'est possible en utilisant le langage MacroXL4 et les formules nommées.

Utiliser LIRE.CELLULE

Ex:

=LIRE.CELLULE(48;Feuil1!A1)

Nommer cette cellule ESTFORMULE

Voir un exemple dans ce fil
https://www.excel-downloads.com/threads/mfc-pour-differencier-formules-et-nombres.113714/


PS: si tu testes de nombreuses cellules, cela risque également de rendre ton classeur lent.

 

[ConsultantJP]

Re : Formule excel qui determine si une celulle contient une formule

bonjour, peux tu me dire comment j'utilise les MacrosXL4 , car j'avais fait, et j'ai essayé le code en live et ça me met une erreur .?

merci

 

[ConsultantJP]

Re : Formule excel qui determine si une celulle contient une formule

*Je viens de retenter , ça me dis fonction non valide ???