XL 2013 VBA formule à l'enregistreur de macro est à déboguer

[Jouxte]

Bonjour à toutes et tous,

J'ai utilisé l'enregistreur de macro pour entrer une formule en P2.

La formule dans le tableau est :
=SI(ESTNUM(CHERCHE("AVIATION";O2));"AVIATION";
SI(ESTNUM(CHERCHE("MOTO";O2));"MOTO";
SI(ESTNUM(CHERCHE("MOTEUR";O2));"MOTEUR";
SI(ESTNUM(CHERCHE("HYDRAULIQUE ";O2));"HYDRAULIQUE ";
SI(ESTNUM(CHERCHE("TRANSMISSION";O2));"TRANSMISSIONS";
SI(ESTNUM(CHERCHE("MULTIFONCTIONNELLE";O2));"MULTIFONCTIONNELLE";
SI(ESTNUM(CHERCHE("GRAISSE";O2));"GRAISSE";
SI(ESTNUM(CHERCHE("GREASE";O2));"GRAISSE";
SI(ESTNUM(CHERCHE("ADBLUE";O2));"ADBLUE";
SI(ESTNUM(CHERCHE("COOL";O2));"LIQUIDE REFROIDISSEMENT";
SI(ESTNUM(CHERCHE("FREIN";O2));"FREIN";
SI(ESTNUM(CHERCHE("BOUTIQUE";O2));"LAVE GLACE";
"IND"))))))))))))

Ce qui se traduit par la code suivant :

Sub Macro2()
'
' Macro2 Macro
'

'
    Range("P2").Select
    ActiveCell.FormulaR1C1 = _
        "=" & Chr(10) & "IF(ISNUMBER(SEARCH(""AVIATION"",RC[-1])),""AVIATION""," & Chr(10) & "IF(ISNUMBER(SEARCH(""MOTO"",RC[-1])),""MOTO""," & Chr(10) & "IF(ISNUMBER(SEARCH(""MOTEUR"",RC[-1])),""MOTEUR""," & Chr(10) & "IF(ISNUMBER(SEARCH(""HYDRAULIQUE "",RC[-1])),""HYDRAULIQUE ""," & Chr(10) & "IF(ISNUMBER(SEARCH(""TRANSMISSION"",RC[-1])),""TRANSMISSIONS""," & Chr(10) & "IF(ISNUMBER(SEARCH(""MULTIFONCTIONNELLE"",RC[-1])),""MULTIFONCTIONNELLE""," & Chr(10) & "IF(ISNUMBER" & _
        """GRAISSE"",RC[-1])),""GRAISSE""," & Chr(10) & "IF(ISNUMBER(SEARCH(""GREASE"",RC[-1])),""GRAISSE""," & Chr(10) & "IF(ISNUMBER(SEARCH(""ADBLUE"",RC[-1])),""ADBLUE""," & Chr(10) & "IF(ISNUMBER(SEARCH(""COOL"",RC[-1])),""LIQUIDE REFROIDISSEMENT""," & Chr(10) & "IF(ISNUMBER(SEARCH(""FREIN"",RC[-1])),""FREIN""," & Chr(10) & "IF(ISNUMBER(SEARCH(""BOUTIQUE"",RC[-1])),""LAVE GLACE""," & Chr(10) & """IND""))))))))))))"
End Sub

Lorsque je lance la macro Il m'est demandé de déboguer.

Que puis-je faire ?

Par avance merci pour vos réponses.

 

[sylvanu]

Bonjour Jouxte,
Essayez cela, ça passe :

Sub Macro2()
    Range("P2").FormulaR1C1 = _
        "=IF(ISNUMBER(SEARCH(""AVIATION"",RC[-1])),""AVIATION""," & Chr(10) & _
        "IF(ISNUMBER(SEARCH(""MOTO"",RC[-1])),""MOTO""," & Chr(10) _
        & "IF(ISNUMBER(SEARCH(""MOTEUR"",RC[-1])),""MOTEUR""," & Chr(10) _
        & "IF(ISNUMBER(SEARCH(""HYDRAULIQUE "",RC[-1])),""HYDRAULIQUE ""," & Chr(10) _
        & "IF(ISNUMBER(SEARCH(""TRANSMISSION"",RC[-1])),""TRANSMISSIONS""," & Chr(10) _
        & "IF(ISNUMBER(SEARCH(""MULTIFONCTIONNELLE"",RC[-1])),""MULTIFONCTIONNELLE""," & Chr(10) _
        & "IF(ISNUMBER(SEARCH(""GRAISSE"",RC[-1])),""GRAISSE""," & Chr(10) _
        & "IF(ISNUMBER(SEARCH(""GREASE"",RC[-1])),""GRAISSE""," & Chr(10) _
        & "IF(ISNUMBER(SEARCH(""ADBLUE"",RC[-1])),""ADBLUE""," & Chr(10) _
        & "IF(ISNUMBER(SEARCH(""COOL"",RC[-1])),""LIQUIDE REFROIDISSEMENT""," & Chr(10) _
        & "IF(ISNUMBER(SEARCH(""FREIN"",RC[-1])),""FREIN""," & Chr(10) _
        & "IF(ISNUMBER(SEARCH(""BOUTIQUE"",RC[-1])),""LAVE GLACE""," & Chr(10) _
        & """IND""))))))))))))"
End Sub

 

[Jouxte]

Merci beaucoup sylvanu.
Ca fonctionne parfaitement.