Impossible de traduire une grosse formule en VBA

Bonjour,

Une autre façon de procéder :

Sub Test()

    Dim Frm As String
    Dim Fe As String

    Fe = "'conditions cos'!"

    Frm = "=IF((IF(VLOOKUP(A4," & Fe & "$A$2:$O$4359,4,FALSE)=""PP"",(Q4*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,5,FALSE)))+(R4*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,6,FALSE)))+(S4*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,7,FALSE)))+ (T4*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,8,FALSE)))+"
    Frm = Frm & "(U4*(VLOOKUP(A4,'conditions cos'!$A$2:$O$4359,9,FALSE)))+(V4*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,12,FALSE)))+(W4*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,11,FALSE)))+(X4*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,13,FALSE)))+ (SUM(AA4:AD4)*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,12,FALSE)))+"
    Frm = Frm & "(((U4-AJ4)/30)*VLOOKUP(A4," & Fe & "$A$2:$O$4359,10,FALSE)),(AF4*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,5,FALSE)))+(AG4*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,6,FALSE)))+(AH4*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,7,FALSE)))+ (AI4*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,8,FALSE)))+"
    Frm = Frm & "(AJ4*(VLOOKUP(A4,'conditions cos'!$A$2:$O$4359,9,FALSE)))+(AK4*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,12,FALSE)))+(AL4*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,11,FALSE)))+(AM4*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,13,FALSE)))+ (SUM(AP4:AS4)*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,12,FALSE)))+"
    Frm = Frm & "(((U4-AJ4)/30)*VLOOKUP(A4," & Fe & " $A$2:$O$4359,10,FALSE))))-P4-IF(VLOOKUP(A4," & Fe & " $A$2:$P$4359,16,FALSE)=""oui"",M4,0)<0,0,(IF(VLOOKUP(A4," & Fe & " $A$2:$O$4359,4,FALSE)=""PP"",(Q4*(VLOOKUP(A4," & Fe & " $A$2:$O$4359,5,FALSE)))+(R4*(VLOOKUP(A4," & Fe & " $A$2:$O$4359,6,FALSE)))+"
    Frm = Frm & "(S4*(VLOOKUP(A4," & Fe & " $A$2:$O$4359,7,FALSE)))+ (T4*(VLOOKUP(A4," & Fe & " $A$2:$O$4359,8,FALSE)))+(U4*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,9,FALSE)))+(V4*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,12,FALSE)))+(W4*(VLOOKUP(A4," & Fe & " $A$2:$O$4359,11,FALSE)))+(X4*(VLOOKUP(A4," & Fe & " $A$2:$O$4359,13,FALSE)))+"
    Frm = Frm & "(SUM(AA4:AD4)*(VLOOKUP(A4," & Fe & " $A$2:$O$4359,12,FALSE)))+(((U4-AJ4)/30)*VLOOKUP(A4," & Fe & "$A$2:$O$4359,10,FALSE)),(AF4*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,5,FE)))+(AG4*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,6,FALSE)))+(AH4*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,7,FALSE)))+ (AI4*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,8,FALSE)))+"
    Frm = Frm & "(AJ4*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,9,FALSE)))+(AK4*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,12,FALSE)))+(AL4*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,11,FALSE)))+(AM4*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,13,FALSE)))+ (SUM(AP4:AS4)*(VLOOKUP(A4," & Fe & "$A$2:$O$4359,12,FALSE)))+(((U4-AJ4)/30)*VLOOKUP(A4," & Fe & "$A$2:$O$4359,10,FALSE))))-P4-IF(VLOOKUP(A4," & Fe & "$A$2:$P$4359,16,FALSE)=""oui"",M4,0))"

    Range("AU4").Formula = Frm

End Sub

Theze, ça marche merci infiniment pour ton aide!