XL 2013 Formule trop longue en vba mais pas en Excel...

siocnarf

XLDnaute Occasionnel
Bonjour,

J'ai la formule suivante en Excel qui fonctionne correctement:

Code:
=SI(F2="Local";SI(ESTNA(RECHERCHEV(C2;Comptes_Systèmes;1;FAUX))=FAUX;"Compte local système";SI(D2="SystemAccount";"Compte local système";"Compte local douteux"));SI(ET(D2="Group";F2="Domain");"Groupe de domaine";SI(ET(D2="SystemAccount";F2="Domain");"Compte système appartenant au domaine";SI(ET(D2="UserAccount";F2="Domain");SI(ESTNA(RECHERCHEV(C2;AD;4;FAUX));"Compte inconnu sur le domaine";RECHERCHEV(C2;AD;4;FAUX))))))

Si je l'enregistre en macro, j'obtiens:
Code:
 ActiveCell.FormulaR1C1 = _
        "=IF(RC[-3]=""Local"",IF(ISNA(VLOOKUP(RC[-6],Comptes_Systèmes,1,FALSE))=FALSE,""Compte local système"",IF(RC[-5]=""SystemAccount"",""Compte local système"",""Compte local douteux"")),IF(AND(RC[-5]=""Group"",RC[-3]=""Domain""),""Groupe de domaine"",IF(AND(RC[-5]=""SystemAccount"",RC[-3]=""Domain""),""Compte système appartenant au domaine"",IF(AND(RC[-5]=""UserAccount""& _
        "=""Domain""),IF(ISNA(VLOOKUP(RC[-6],AD,4,FALSE)),""Compte inconnu sur le domaine"",VLOOKUP(RC[-6],AD,4,FALSE))))))"

Et elle sort en rouge.
Comment traiter ce genre de situation car il semble que pour une macro se soit trop long?!

Merci,

François
 

eriiic

XLDnaute Barbatruc
Bonjour,

Si tu peux te passer de la notation R1C1 avec .Formula ça passe :
VB:
ActiveCell.Formula = "=IF(F2=""Local"",IF(ISNA(VLOOKUP(C2,Comptes_Systèmes,1,FALSE))=FALSE,""Compte local système"",IF(D2=""SystemAccount"",""Compte local système"",""Compte local douteux"")),IF(AND(D2=""Group"",F2=""Domain""),""Groupe de domaine"",IF(AND(D2=""SystemAccount"",F2=""Domain""),""Compte système appartenant au domaine"",IF(AND(D2=""UserAccount"",F2=""Domain""),IF(ISNA(VLOOKUP(C2,AD,4,FALSE)),""Compte inconnu sur le domaine"",VLOOKUP(C2,AD,4,FALSE))))))"
Tu peux aussi raccourcir ta formule et la rendre plus lisible en remplaçant tes SI(ESTNA(... par des SIERREUR(... qui évite de répéter la fonction.
eric
 

job75

XLDnaute Barbatruc
Bonjour siocnarf, eriiii,

L'enregistreur de macro en scindant la formule a fait une erreur, il manque des choses juste après ""UserAccount"".

La formule R1C1 corrigée et plus "lisible" :
Code:
ActiveCell.FormulaR1C1 = _
    "=IF(RC[-3]=""Local"",IF(ISNA(VLOOKUP(RC[-6],Comptes_Systèmes,1,FALSE))=FALSE,""Compte local système""," & _
    "IF(RC[-5]=""SystemAccount"",""Compte local système"",""Compte local douteux""))," & _
    "IF(AND(RC[-5]=""Group"",RC[-3]=""Domain""),""Groupe de domaine""," & _
    "IF(AND(RC[-5]=""SystemAccount"",RC[-3]=""Domain""),""Compte système appartenant au domaine""," & _
    "IF(AND(RC[-5]=""UserAccount"",RC[-3]=""Domain"")," & _
    "IF(ISNA(VLOOKUP(RC[-6],AD,4,FALSE)),""Compte inconnu sur le domaine"",VLOOKUP(RC[-6],AD,4,FALSE))))))"
A+
 

bof

XLDnaute Occasionnel
bonjour,
Le mieux et le plus simple c'est de Copier/Coller ta formule dans le Gestionnaire de nom à partir de la cellule "source"
Tu lui donnes le nom que tu veux ("PAPILLON" par exemple.)
ensuite la macro :
Code:
Sub Bouton1_Cliquer()
ActiveCell = "=PAPILLON"
End Sub
Elle est pas belle la vie ?
 

Pièces jointes

  • CaptureFormule.xlsm
    15.6 KB · Affichages: 59

bof

XLDnaute Occasionnel
job75 à dit:
Oui, surtout quand on se la simplifie en ne répondant pas au problème posé !A+
Bonjour,
Je ne suis peut-être pas un barbatruc, mais pas non plus un idiot :
Je ne répond pas à la question mais tu as quand même été capable de l'interpréter à ta sauce pour faire grimper ton applaudimètre...
L'avantage de mettre la formule dans le Gestionnaire de nom est qu'ensuite tu ne fais référence qu'au nom choisi :
En cas de suppression de la cellule (ou de la ligne de référence la macro fonctionne quand même.
A+
 

job75

XLDnaute Barbatruc
Re,

Alors je continue ma sauce.

Si la formule en I2 n'est pas éternelle, soit qu'on supprime I2, soit qu'on supprime la feuille où elle se trouve, on peut la stocker quelque part pour la sauvegarder.

Son stockage dans un nom défini est une bonne solution, mais en la stockant sous forme de textes concaténés.

Voyez le fichier joint et ces 2 macros :
Code:
Sub StockerFormule()
Dim f$
f = "=""" & Mid(Replace(ActiveCell.FormulaR1C1, """", "µ"), 2) & """"
f = Replace(f, ",IF", ",""&""IF") 'scinde la formule car elle dépasse 255 caractères
ThisWorkbook.Names.Add "MaFormule", f, Visible:=True 'False pour masquer le nom défini
End Sub

Sub EntrerFormule()
Dim f$
f = ThisWorkbook.Names("MaFormule").RefersTo
f = Replace(Replace(Replace(f, """&""", ""), """", ""), "µ", """")
ActiveCell = f
End Sub
A+
 

Pièces jointes

  • Formule R1C1 stockée sous forme de textes concaténés(1).xlsm
    20.9 KB · Affichages: 48

job75

XLDnaute Barbatruc
Bonsoir eriiiic,

C'est un texte dans une formule qui ne peut pas avoir plus de 255 caractères.

Quand on définit un nom par ="XXX..." ce qui est entre guillemets ne peut pas avoir plus de 255 caractères.

Dans ma macro StockerFormule neutralise pour mieux comprendre la ligne :
Code:
f = Replace(f, ",IF", ",""&""IF") 'scinde la formule car elle dépasse 255 caractères
A+
 

job75

XLDnaute Barbatruc
Bonjour le fil, le forum,

Ce code est un peu plus élaboré :
Code:
Sub CopierFormule()
'se lance par Ctrl+M
Dim f$
On Error Resume Next
f = ThisWorkbook.Names("MaFormule").RefersTo
On Error GoTo 0
If f = "" Then
  f = "=""" & Mid(Replace(ActiveCell.FormulaR1C1, """", "µ"), 2) & """"
  f = Replace(f, ",IF", ",""&""IF") 'scinde la formule car le texte dépasse 255 caractères
  ThisWorkbook.Names.Add "MaFormule", f, Visible:=False 'True pour afficher le nom défini
End If
f = Replace(Replace(Replace(f, """&""", ""), """", ""), "µ", """")
ActiveCell = f
End Sub

Sub SupprimerFormule()
On Error Resume Next
If ThisWorkbook.Names("MaFormule").RefersTo = "" Then Else _
  If MsgBox("Êtes-vous sûr de vouloir supprimer la formule mémorisée ?", 52) = 6 _
    Then ThisWorkbook.Names("MaFormule").Delete
End Sub
Fichier (2).

Bonne journée.
 

Pièces jointes

  • Formule R1C1 stockée sous forme de textes concaténés(2).xlsm
    23.6 KB · Affichages: 58

Discussions similaires

Réponses
1
Affichages
1 K

Statistiques des forums

Discussions
311 712
Messages
2 081 802
Membres
101 819
dernier inscrit
lukumubarth