Trouver position d'une formule perso
Bonjour,
Alors voila je reprend depuis quelques temps un fichier Excel bien fournis que cela soit en programmation VBA que l'excel lui même.
Et je voudrais savoir si quelqu'un possède, a trouver ou sait comment faire une petite fonction pour trouver les positions de cellules qui utilise une fonction spécifique ?
Je m'explique par exemple je créé une fonction, intitulé "ToTo_exemple(...)" qui calcul quelques choses de spécifique.
J'attribue cette fonction à 3 cellules dans mon Excel.
Je voudrais faire une seconde function qui me permet soit d'afficher dans une MsgBox ou alors dans une cellule (c'est du peaufinage ça) les cellules qui utilisent "ToTo_exemple". En d'autres termes me dirais "A18, C56, D78".
J'ai fait beaucoup de recherche et j'ai trouvé pour les fonctions Excel de base, mais pas pour les fonctions personnelles.
Je voudrais surtout savoir s'il y a une autre façon de faire que la basique :
A partir de A1 jusqu'à la fin du UsedRange
Si la formule de la case courante contient "ToTo" alors enregistrer ligne et colonne fin de SI
ligne +1
?
Merci beaucoup si quelqu'un peut m'aider.
Cordialement.
Merci de ta réponse.
En fait mon idée serait de faire un traitement par la suite de ces positions, par exemple je modifie ma fonction qui prend 1 argument de plus, au lieu de me faire les 130 cellules à la main (c'est mon cas) trouvé les positions et à partir des positions j'ai déjà une fonction qui modifie la formule de la fonction associé.
J'ai essayé de faire un premier jet avec la logique de base (sans succès j'avoue) :
Sub exemple()
Dim WS_Count As Integer
Dim i, j, k, compt As Integer
Dim tab_position(2, 1024)
Dim texte As String
compt = 1
WS_Count = ThisWorkbook.Worksheets.count
' Begin the loop.
For i = 1 To WS_Count
For j = 1 To ThisWorkbook.Worksheets(i).UsedRange.Columns.count
For k = 1 To ThisWorkbook.Worksheets(i).UsedRange.Rows.count
If (ThisWorkbook.Worksheets(i).Cells(k, j).Formula Like "*GetLine*") Then
tab_position(1, compt) = k
tab_position(2, compt) = j
End If
Next k
Next j
Next i
' chr(64 + col) pour convertir entier en lettre colonne si besoin d'afficher la colonne en lettre
For i = 1 To compt
texte = texte & CStr(tab_position(1, i)) & CStr(tab_position(2, i)) & vbCrLf
Next i
MsgBox (texte)
End Sub
Salut
ça m'intéresse ce truc : tu peux nous en dire plus à propos de ce changement de formule ?
For Each rCel In UsedRange
If rCel.HasFormula = True Then
If InStr(rCel.Formula, "A") > 0 Then rCel.FormulaLocal = "=XXX"
End If
Next
A+
Bonjour,
oui avec plaisir si tu veux, j'améliore un logiciel de comptabilité, 16 feuilles Excel d'environ 250 lignes chacunes globalement.
Et l'ancienne personne était adepte de faire un calcul dans une case réutiliser ce résultat dans un autre calcul dans une autre case ainsi de suite jusqu'à 6-7 cases avant d'avoir vraiment le résultat utilisé et interessant.
Donc mon but est de remplacer les formules qui prennent que 2 arguments par une qui en prend 7-8 du premier coup sans pollution pour l'utilisateur.
Donc pour le moment je faisais la technique un peu simple d'indiquer le range et de lui dire .Range(cl & ln).FormulaLocal = "=...."
mais si ton truc marche, j'aurais dans mon tableau toutes les positions des cellules utilisant cette fonction dans un premier temps.
Et dans un second, je pourrais parcourir chacune des cases cibles et changé ma formule.
Je sais pas si c'est la meilleure option mais je vais pousser celle la pour l'instant.
Merci de ton aide en tout cas.
Salut tenders_vba,
ok, ça, c'est le décor! Bel objectif!
En dire plus, c'est aussi donner des infos plus "techniques".
Donne-nous, si tu veux bien, un exemple de feuille anonymisé avec cet ensemble de "sous-formules" et la formule finale que tu souhaites... ou est-ce trop compliqué, mathématiquement parlant, à mettre au point sans être comptable soi-même ?
Tu préfères peut-être simplement une inputbox pour faire une recherche ciblée dans les formules au cas par cas ?
La balle est dans ton camp...
A+
Bonjour,
un exemple, pour afficher les adresses des cellules contenant "toto(" dans leur formule
Sub aargh()
Set re = Cells.Find("toto(", lookat:=xlPart, LookIn:=xlFormulas, MatchCase:=False)
If Not re Is Nothing Then
fa = re.Address
Do
msg = msg & re.Address & ","
Set re = Cells.FindNext(re)
Loop Until re.Address = fa
End If
MsgBox Left(msg, Len(msg) - 1)
End Sub
Je peux donner plus d'informations en simplifiant grandement la chose au point que ca va paraitre enfantin.
Je vais prendre un exemple mais j'en ai plusieurs.
J'ai une fonction
GetPrx( str_fiscale As String, mode As Range, ByVal rg_ln As Range) As Variant
J'ai sur une feuille de mon Excel l'augmentation du prix d'un produit chaque année.
Il faut savoir qu'il y a plusieurs mode de calcul d'un prix selon des options qui sont caractérisé par des modes.
Donc selon l'année fiscale, le mode saisi, et le dernier c'est pour savoir où cherché (selon la référence du produit), ma fonction va calculer le prix final. (selon différents paramètres, avec toute la sécurité nécessaire vérifier que chaque info est dans le bon format ....)
J'ai à la modifié, c'est à dire que ce calcul va dépendre désormais d'une autre range qui sera une option que va saisir l'utilisateur, si il veut oui ou non étendre la garantie (donc augmenter le prix) et pour bénéficier de la synchro VBA, il faut passer en argument de la fonction la cellule que l'on veut. Donc je dois modifier ma fonction comme cela :
GetPrx( str_fiscale As String, mode As Range, ByVal rg_ln As Range, warranly As Range) As Variant
Puisque cette fonction est utilisé dans 979 cellules (je viens de compter), je voulais :
- Enregistrer les positions de ces dernières pour tracer un historique des modifications
- Modifier la fonction donc recopier toute la formule en dure avec une string
Comment modifier la formule d'une cellule donnée est simple, va etre composé de :
Range.FormulaLocal = "=GetPrx(" & rg_fiscale & "," & rg_mode & "," & rg_ln & "," &rg_warr & ")"
à savoir que le traitement des rg_... est effectué avant, retrouve avec la ligne où est appelé la fonction la référence qui se situe à une colonne constante sur la même ligne, puis avec cette référence retrouve toutes les informations nécessaire des différents onglets par un principe de dictionnaire.
Ce que je veux désormais est la partie qui me parcours toutes les cellules de toutes les feuilles et me dis dans quelles cellules se trouve le nom de la fonction GetPrx . Ces cellules sont donc référencées par un onglet, une ligne et une colonne. Comme cela je pourrais tracer l'historique des modifications, faire les vérifications des modifications et aussi comme ca j'ai pas a refaire ma fonction qui modifie la formule donnée.
J'ai l'impression que j'ai fait le plus dure mais j'arrive pas à tout simplement sortir les positions des cellules à modifiées.....
Sub exemple()
Dim WS_Count As Integer
Dim i, compt As Integer
Dim tab_position(3, 2000)
Dim texte, nomrecherche As String
Dim rCel As Range
nomrecherche = "GetPrx"
compt = 1
WS_Count = ThisWorkbook.Worksheets.count
' Begin the loop.
For i = 1 To WS_Count
ThisWorkbook.Worksheets(i).Unprotect
For Each rCel In Worksheets(i).UsedRange
If rCel.HasFormula = True Then
If InStr(rCel.Formula, nomrecherche) > 0 Then
tab_position(1, compt) = i
tab_position(2, compt) = rCel.Rows
tab_position(3, compt) = rCel.Columns
compt = compt + 1
ChangeFormule i, rCel.Rows, rCel.Columns
End If
End If
Next
ThisWorkbook.Worksheets(i).Protect
Next i
' chr(64 + col) pour convertir entier en lettre colonne si besoin d'afficher la colonne en lettre
For i = 1 To compt
texte = texte & CStr(tab_position(1, i)) & CStr(tab_position(2, i)) & CStr(tab_position(3, i)) & vbCrLf
Next i
MsgBox (texte)
End Sub
Bonjour,
un exemple, pour afficher les adresses des cellules contenant "toto(" dans leur formule
Sub aargh() Set re = Cells.Find("toto(", lookat:=xlPart, LookIn:=xlFormulas, MatchCase:=False) If Not re Is Nothing Then fa = re.Address Do msg = msg & re.Address & "," Set re = Cells.FindNext(re) Loop Until re.Address = fa End If MsgBox Left(msg, Len(msg) - 1) End Sub
Je connaissais pas ces fonctionnalités merci beaucoup.
Mais est-ce que le FindNext passe d'un onglet à l'autre ou est-il nécessaire de faire une boucle pour chaque onglet ?