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