Probleme macro enregistrée

Bonjours a tous,

j ai enregistré cette macro avec l enregistreur et je ne comprends pas pourquoi le resultat donnne #NOM? Si apres je retourne sur la cellule et je valide la formule avec "enter" alors #NOM? disparait et le calcul s effecture correctement (sans rien toucher du tout a la formule)

Je deja fais la manip avec plusieurs autres formules du meme type sans probleme, mais avec celle la, pas moyen !!

je joins un fichier

Flo

Bonjour,

Un essai ...

Sub CUPIE()
   '
   ' CUPIE Macro

   Range("CU46").FormulaR1C1 = _
   "=IF(OR(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RC[-85],""B"",""""),""R"",""""),""T"",""""),""N"",""""),""M"",""""),""O"",""""),""V"",""""),""W"",""""),""G"",""""))>0,LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RC[-84],""B"",""""),""R"",""""),""T" & _
                               """,""""),""N"",""""),""M"",""""),""O"",""""),""V"",""""),""W"",""""),""G"",""""))>0,LEN(RC[-85])-LEN(SUBSTITUTE(RC[-85],""B"",""""))+LEN(RC[-84])-LEN(SUBSTITUTE(RC[-84],""B"",""""))>2,LEN(RC[-85])-LEN(SUBSTITUTE(RC[-85],""B"",""""))>1,LEN(RC[-84])-LEN(SUBSTITUTE(RC[-84],""B"",""""))>1,LEN(RC[-85])-LEN(SUBSTITUTE(RC[-85],""R"",""""))+LEN(RC[-84])-LEN(SUBSTITUTE(RC[-8" & _
                               "4],""R"",""""))>2,LEN(RC[-85])-LEN(SUBSTITUTE(RC[-85],""T"",""""))+LEN(RC[-84])-LEN(SUBSTITUTE(RC[-84],""T"",""""))>2,LEN(RC[-85])-LEN(SUBSTITUTE(RC[-85],""N"",""""))+LEN(RC[-84])-LEN(SUBSTITUTE(RC[-84],""N"",""""))>1,LEN(RC[-85])-LEN(SUBSTITUTE(RC[-85],""M"",""""))+LEN(RC[-84])-LEN(SUBSTITUTE(RC[-84],""M"",""""))>2,LEN(RC[-85])-LEN(SUBSTITUTE(RC[-85],""O"",""""))+L" & _
                               "EN(RC[-84])-LEN(SUBSTITUTE(RC[-84],""O"",""""))>2,LEN(RC[-85])-LEN(SUBSTITUTE(RC[-85],""W"",""""))+LEN(RC[-84])-LEN(SUBSTITUTE(RC[-84],""W"",""""))>2,LEN(RC[-85])-LEN(SUBSTITUTE(RC[-85],""G"",""""))+LEN(RC[-84])-LEN(SUBSTITUTE(RC[-84],""G"",""""))>1,LEN(RC[-85])-LEN(SUBSTITUTE(RC[-85],""V"",""""))+LEN(RC[-84])-LEN(SUBSTITUTE(RC[-84],""V"",""""))>1),1,0)" & ""

   Range("CU46").Select
   Selection.AutoFill Destination:=Range("CU46:CU138")

End Sub

ric

Bonjour

Le code corrigé

Sub CUPIE()
 Range("CU46").FormulaR1C1 = _
"=IF(OR(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RC[-85],""B"",""""),""R"",""""),""T"",""""),""N"",""""),""M"",""""),""O"",""""),""V"",""""),""W"",""""),""G"",""""))>0,LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RC[-84],""B"",""""),""R"",""""),""T" & _
""",""""),""N"",""""),""M"",""""),""O"",""""),""V"",""""),""W"",""""),""G"",""""))>0,LEN(RC[-85])-LEN(SUBSTITUTE(RC[-85],""B"",""""))+LEN(RC[-84])-LEN(SUBSTITUTE(RC[-84],""B"",""""))>2,LEN(RC[-85])-LEN(SUBSTITUTE(RC[-85],""B"",""""))>1,LEN(RC[-84])-LEN(SUBSTITUTE(RC[-84],""B"",""""))>1,LEN(RC[-85])-LEN(SUBSTITUTE(RC[-85],""R"",""""))+LEN(RC[-84])-LEN(SUBSTITUTE(RC[-8" & _
"4],""R"",""""))>2,LEN(RC[-85])-LEN(SUBSTITUTE(RC[-85],""T"",""""))+LEN(RC[-84])-LEN(SUBSTITUTE(RC[-84],""T"",""""))>2,LEN(RC[-85])-LEN(SUBSTITUTE(RC[-85],""N"",""""))+LEN(RC[-84])-LEN(SUBSTITUTE(RC[-84],""N"",""""))>1,LEN(RC[-85])-LEN(SUBSTITUTE(RC[-85],""M"",""""))+LEN(RC[-84])-LEN(SUBSTITUTE(RC[-84],""M"",""""))>2,LEN(RC[-85])-LEN(SUBSTITUTE(RC[-85],""O"",""""))+L" & _
"EN(RC[-84])-LEN(SUBSTITUTE(RC[-84],""O"",""""))>2,LEN(RC[-85])-LEN(SUBSTITUTE(RC[-85],""W"",""""))+LEN(RC[-84])-LEN(SUBSTITUTE(RC[-84],""W"",""""))>2,LEN(RC[-85])-LEN(SUBSTITUTE(RC[-85],""G"",""""))+LEN(RC[-84])-LEN(SUBSTITUTE(RC[-84],""G"",""""))>1,LEN(RC[-85])-LEN(SUBSTITUTE(RC[-85],""V"",""""))+LEN(RC[-84])-LEN(SUBSTITUTE(RC[-84],""V"",""""))>1),1,0)" & _
""

Range("CU46").AutoFill Destination:=Range("CU46:CU138")

End Sub

Cordialement

PS : n'oubliez pas de cloturer le fil si ok

super, merci a vous 2, ca marche impec, je me souviendrais de FormulaR1C1 la prochaine fois.

Par contre une question de novice...Pourquoi lors de l enregistrement automatique de macro, pour les tres longues formules, lorsque l editeur VBA retourne a la ligne avec "& _ " alors quand j ouvre le code par la suite, il manque toujours des bouts de formules apres les sauts de lignes ? il manque toujours 4 ou 5 caracteres. Ca vient d un parametre a changer dans l editeur ?

Merci bonne fin de journee a tous