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

[siocnarf]

Bonjour,

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

=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:

 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]

Bonjour,

Si tu peux te passer de la notation R1C1 avec .Formula ça passe :

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]

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" :

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]

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 :

Sub Bouton1_Cliquer()
ActiveCell = "=PAPILLON"
End Sub

Elle est pas belle la vie ?

 

[job75]

Bonjour à tous,

Elle est pas belle la vie ?

Oui, surtout quand on se la simplifie en ne répondant pas au problème posé !

A+

 

[job75]

Re,

Et on se simplifie encore plus la vie en copiant la formule de la cellule I2 où a priori se trouve la formule du post #1 :

Sub a()
ActiveCell = [I2].FormulaR1C1
End Sub

A+

 

[bof]

Bonjour à tous,

Oui, surtout quand on se la simplifie en ne répondant pas au problème posé !

A+

Re,

Et on se simplifie encore plus la vie en copiant la formule de la cellule I2 où a priori se trouve la formule du post #1 :

Sub a()
ActiveCell = [I2].FormulaR1C1
End Sub

A+

 

[bof]

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]

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 :

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+

 

[eriiic]

Bonjour,

Pourquoi ça ?
La limite dans un nom est beaucoup plus que 255 caractères. 2000 déjà ça rentre.
eric

 

[job75]

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 :

f = Replace(f, ",IF", ",""&""IF") 'scinde la formule car elle dépasse 255 caractères

A+

 

[eriiic]

Ah ok.
Mais vu que sa formule ne fait que 422 caractères je l'avais rentrée directement dans le nom.

 

[job75]

Bonjour le fil, le forum,

Ce code est un peu plus élaboré :

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.