Sub Formule_Auto()
Dim DerLig As Long
Application.ScreenUpdating = False
Range("M6:S10000").Clear
DerLig = Range("B" & Rows.Count).End(xlUp).Row
Range("M6:M" & DerLig).FormulaR1C1 = "=RC[-6]"
Range("N6:N" & DerLig).FormulaR1C1 = "=IF(AND(RC[-6]>0,RC[-7]=0),RC[-6],0)"
Range("O6:O" & DerLig).FormulaR1C1 = "=IF(IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-8],0)<0,IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-7],0),IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-7],0)-IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-8],0))"
Range("P6:P" & DerLig).FormulaR1C1 = "=IF(OR(AND(RC[-9]>0,RC[-8]=0),AND(RC[-9]>0,RC[-8]<0)),-RC[-9],0)"...
Sub Formule_Auto()
Dim DerLig As Long
Application.ScreenUpdating = False
Range("M6:S10000").Clear
DerLig = Range("B" & Rows.Count).End(xlUp).Row
Range("M6:M" & DerLig).FormulaR1C1 = "=RC[-6]"
Range("N6:N" & DerLig).FormulaR1C1 = "=IF(AND(RC[-6]>0,RC[-7]=0),RC[-6],0)"
Range("O6:O" & DerLig).FormulaR1C1 = "=IF(IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-8],0)<0,IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-7],0),IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-7],0)-IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-8],0))"
Range("P6:P" & DerLig).FormulaR1C1 = "=IF(OR(AND(RC[-9]>0,RC[-8]=0),AND(RC[-9]>0,RC[-8]<0)),-RC[-9],0)"
Range("Q6:Q" & DerLig).FormulaR1C1 = "=IF(RC[-9]<0,RC[-9],0)"
Range("R6:R" & DerLig).FormulaR1C1 = "=IF(RC[-11]<0,-RC[-11],0)"
Range("S6:S" & DerLig).FormulaR1C1 = "=RC[-11]"
Range("M3:S3").FormulaR1C1 = "=SUM(R[3]C:R[22]C)"
Range("M3:S" & DerLig).Value = Range("M3:S" & DerLig).Value 'Remplacement des formules par les valeurs
'Format
Range("M6:S" & DerLig).Interior.Color = RGB(238, 236, 225)
Range("M6:S" & DerLig).Borders(xlEdgeLeft).Weight = xlMedium
Range("M6:S" & DerLig).Borders(xlEdgeTop).Weight = xlMedium
Range("M6:S" & DerLig).Borders(xlEdgeRight).Weight = xlMedium
End Sub
Super merci beaucoup @RougeBonjour,
Avec l'aide du VBA:
VB:Sub Formule_Auto() Dim DerLig As Long Application.ScreenUpdating = False Range("M6:S10000").Clear DerLig = Range("B" & Rows.Count).End(xlUp).Row Range("M6:M" & DerLig).FormulaR1C1 = "=RC[-6]" Range("N6:N" & DerLig).FormulaR1C1 = "=IF(AND(RC[-6]>0,RC[-7]=0),RC[-6],0)" Range("O6:O" & DerLig).FormulaR1C1 = "=IF(IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-8],0)<0,IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-7],0),IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-7],0)-IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-8],0))" Range("P6:P" & DerLig).FormulaR1C1 = "=IF(OR(AND(RC[-9]>0,RC[-8]=0),AND(RC[-9]>0,RC[-8]<0)),-RC[-9],0)" Range("Q6:Q" & DerLig).FormulaR1C1 = "=IF(RC[-9]<0,RC[-9],0)" Range("R6:R" & DerLig).FormulaR1C1 = "=IF(RC[-11]<0,-RC[-11],0)" Range("S6:S" & DerLig).FormulaR1C1 = "=RC[-11]" Range("M3:S3").FormulaR1C1 = "=SUM(R[3]C:R[22]C)" Range("M3:S" & DerLig).Value = Range("M3:S" & DerLig).Value 'Remplacement des formules par les valeurs 'Format Range("M6:S" & DerLig).Interior.Color = RGB(238, 236, 225) Range("M6:S" & DerLig).Borders(xlEdgeLeft).Weight = xlMedium Range("M6:S" & DerLig).Borders(xlEdgeTop).Weight = xlMedium Range("M6:S" & DerLig).Borders(xlEdgeRight).Weight = xlMedium End Sub
Cdlt
Bonjour,
Avec l'aide du VBA:
VB:Sub Formule_Auto() Dim DerLig As Long Application.ScreenUpdating = False Range("M6:S10000").Clear DerLig = Range("B" & Rows.Count).End(xlUp).Row Range("M6:M" & DerLig).FormulaR1C1 = "=RC[-6]" Range("N6:N" & DerLig).FormulaR1C1 = "=IF(AND(RC[-6]>0,RC[-7]=0),RC[-6],0)" Range("O6:O" & DerLig).FormulaR1C1 = "=IF(IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-8],0)<0,IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-7],0),IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-7],0)-IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-8],0))" Range("P6:P" & DerLig).FormulaR1C1 = "=IF(OR(AND(RC[-9]>0,RC[-8]=0),AND(RC[-9]>0,RC[-8]<0)),-RC[-9],0)" Range("Q6:Q" & DerLig).FormulaR1C1 = "=IF(RC[-9]<0,RC[-9],0)" Range("R6:R" & DerLig).FormulaR1C1 = "=IF(RC[-11]<0,-RC[-11],0)" Range("S6:S" & DerLig).FormulaR1C1 = "=RC[-11]" Range("M3:S3").FormulaR1C1 = "=SUM(R[3]C:R[22]C)" Range("M3:S" & DerLig).Value = Range("M3:S" & DerLig).Value 'Remplacement des formules par les valeurs 'Format Range("M6:S" & DerLig).Interior.Color = RGB(238, 236, 225) Range("M6:S" & DerLig).Borders(xlEdgeLeft).Weight = xlMedium Range("M6:S" & DerLig).Borders(xlEdgeTop).Weight = xlMedium Range("M6:S" & DerLig).Borders(xlEdgeRight).Weight = xlMedium End Sub
Cdlt
M3 jusqu'à S3
Sub Formule_Auto()
Dim DerLig As Long
Application.ScreenUpdating = False
Range("M6:S10000").Clear
DerLig = Range("B" & Rows.Count).End(xlUp).Row
Range("M6:M" & DerLig).FormulaR1C1 = "=RC[-6]"
Range("N6:N" & DerLig).FormulaR1C1 = "=IF(AND(RC[-6]>0,RC[-7]=0),RC[-6],0)"
Range("O6:O" & DerLig).FormulaR1C1 = "=IF(IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-8],0)<0,IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-7],0),IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-7],0)-IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-8],0))"
Range("P6:P" & DerLig).FormulaR1C1 = "=IF(OR(AND(RC[-9]>0,RC[-8]=0),AND(RC[-9]>0,RC[-8]<0)),-RC[-9],0)"
Range("Q6:Q" & DerLig).FormulaR1C1 = "=IF(RC[-9]<0,RC[-9],0)"
Range("R6:R" & DerLig).FormulaR1C1 = "=IF(RC[-11]<0,-RC[-11],0)"
Range("S6:S" & DerLig).FormulaR1C1 = "=RC[-11]"
Range("M3:S3").FormulaR1C1 = "=SUM(R[3]C:R" & DerLig & "C)"
Range("M3:S" & DerLig).Value = Range("M3:S" & DerLig).Value 'Remplacement des formules par les valeurs
'Format
Range("M6:S" & DerLig).Interior.Color = RGB(238, 236, 225)
Range("M6:S" & DerLig).Borders(xlEdgeLeft).Weight = xlMedium
Range("M6:S" & DerLig).Borders(xlEdgeTop).Weight = xlMedium
Range("M6:S" & DerLig).Borders(xlEdgeRight).Weight = xlMedium
End Sub
Super merci beaucoup @Rouge Merci le forumpardon, petit oubli, voici le corrigé
Code:Sub Formule_Auto() Dim DerLig As Long Application.ScreenUpdating = False Range("M6:S10000").Clear DerLig = Range("B" & Rows.Count).End(xlUp).Row Range("M6:M" & DerLig).FormulaR1C1 = "=RC[-6]" Range("N6:N" & DerLig).FormulaR1C1 = "=IF(AND(RC[-6]>0,RC[-7]=0),RC[-6],0)" Range("O6:O" & DerLig).FormulaR1C1 = "=IF(IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-8],0)<0,IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-7],0),IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-7],0)-IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-8],0))" Range("P6:P" & DerLig).FormulaR1C1 = "=IF(OR(AND(RC[-9]>0,RC[-8]=0),AND(RC[-9]>0,RC[-8]<0)),-RC[-9],0)" Range("Q6:Q" & DerLig).FormulaR1C1 = "=IF(RC[-9]<0,RC[-9],0)" Range("R6:R" & DerLig).FormulaR1C1 = "=IF(RC[-11]<0,-RC[-11],0)" Range("S6:S" & DerLig).FormulaR1C1 = "=RC[-11]" Range("M3:S3").FormulaR1C1 = "=SUM(R[3]C:R" & DerLig & "C)" Range("M3:S" & DerLig).Value = Range("M3:S" & DerLig).Value 'Remplacement des formules par les valeurs 'Format Range("M6:S" & DerLig).Interior.Color = RGB(238, 236, 225) Range("M6:S" & DerLig).Borders(xlEdgeLeft).Weight = xlMedium Range("M6:S" & DerLig).Borders(xlEdgeTop).Weight = xlMedium Range("M6:S" & DerLig).Borders(xlEdgeRight).Weight = xlMedium End Sub
pardon, petit oubli, voici le corrigé
Code:Sub Formule_Auto() Dim DerLig As Long Application.ScreenUpdating = False Range("M6:S10000").Clear DerLig = Range("B" & Rows.Count).End(xlUp).Row Range("M6:M" & DerLig).FormulaR1C1 = "=RC[-6]" Range("N6:N" & DerLig).FormulaR1C1 = "=IF(AND(RC[-6]>0,RC[-7]=0),RC[-6],0)" Range("O6:O" & DerLig).FormulaR1C1 = "=IF(IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-8],0)<0,IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-7],0),IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-7],0)-IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-8],0))" Range("P6:P" & DerLig).FormulaR1C1 = "=IF(OR(AND(RC[-9]>0,RC[-8]=0),AND(RC[-9]>0,RC[-8]<0)),-RC[-9],0)" Range("Q6:Q" & DerLig).FormulaR1C1 = "=IF(RC[-9]<0,RC[-9],0)" Range("R6:R" & DerLig).FormulaR1C1 = "=IF(RC[-11]<0,-RC[-11],0)" Range("S6:S" & DerLig).FormulaR1C1 = "=RC[-11]" Range("M3:S3").FormulaR1C1 = "=SUM(R[3]C:R" & DerLig & "C)" Range("M3:S" & DerLig).Value = Range("M3:S" & DerLig).Value 'Remplacement des formules par les valeurs 'Format Range("M6:S" & DerLig).Interior.Color = RGB(238, 236, 225) Range("M6:S" & DerLig).Borders(xlEdgeLeft).Weight = xlMedium Range("M6:S" & DerLig).Borders(xlEdgeTop).Weight = xlMedium Range("M6:S" & DerLig).Borders(xlEdgeRight).Weight = xlMedium End Sub
Range("M6:S10000").Clear
Sub Formule_Auto()
Dim DerLig As Long
Application.ScreenUpdating = False
DerLig = Cells.Find("*", , , , xlByRows, xlPrevious).Row
Range("M6:S" & DerLig).Clear
DerLig = Range("B" & Rows.Count).End(xlUp).Row
Range("M6:M" & DerLig).FormulaR1C1 = "=RC[-6]"
Range("N6:N" & DerLig).FormulaR1C1 = "=IF(AND(RC[-6]>0,RC[-7]=0),RC[-6],0)"
Range("O6:O" & DerLig).FormulaR1C1 = "=IF(IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-8],0)<0,IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-7],0),IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-7],0)-IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-8],0))"
Range("P6:P" & DerLig).FormulaR1C1 = "=IF(OR(AND(RC[-9]>0,RC[-8]=0),AND(RC[-9]>0,RC[-8]<0)),-RC[-9],0)"
Range("Q6:Q" & DerLig).FormulaR1C1 = "=IF(RC[-9]<0,RC[-9],0)"
Range("R6:R" & DerLig).FormulaR1C1 = "=IF(RC[-11]<0,-RC[-11],0)"
Range("S6:S" & DerLig).FormulaR1C1 = "=RC[-11]"
Range("M3:S3").FormulaR1C1 = "=SUM(R[3]C:R" & DerLig & "C)"
Range("M3:S" & DerLig).Value = Range("M3:S" & DerLig).Value 'Remplacement des formules par les valeurs
'Format
Range("M6:S" & DerLig).Interior.Color = RGB(238, 236, 225)
Range("M6:S" & DerLig).Borders(xlEdgeLeft).Weight = xlMedium
Range("M6:S" & DerLig).Borders(xlEdgeTop).Weight = xlMedium
Range("M6:S" & DerLig).Borders(xlEdgeRight).Weight = xlMedium
End Sub
Merci beaucoup @RougeBonjour,
Essayez ceci
VB:Sub Formule_Auto() Dim DerLig As Long Application.ScreenUpdating = False DerLig = Cells.Find("*", , , , xlByRows, xlPrevious).Row Range("M6:S" & DerLig).Clear DerLig = Range("B" & Rows.Count).End(xlUp).Row Range("M6:M" & DerLig).FormulaR1C1 = "=RC[-6]" Range("N6:N" & DerLig).FormulaR1C1 = "=IF(AND(RC[-6]>0,RC[-7]=0),RC[-6],0)" Range("O6:O" & DerLig).FormulaR1C1 = "=IF(IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-8],0)<0,IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-7],0),IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-7],0)-IF(OR(AND(RC[-8]>0,RC[-7]>0),AND(RC[-8]<0,RC[-7]>0)),RC[-8],0))" Range("P6:P" & DerLig).FormulaR1C1 = "=IF(OR(AND(RC[-9]>0,RC[-8]=0),AND(RC[-9]>0,RC[-8]<0)),-RC[-9],0)" Range("Q6:Q" & DerLig).FormulaR1C1 = "=IF(RC[-9]<0,RC[-9],0)" Range("R6:R" & DerLig).FormulaR1C1 = "=IF(RC[-11]<0,-RC[-11],0)" Range("S6:S" & DerLig).FormulaR1C1 = "=RC[-11]" Range("M3:S3").FormulaR1C1 = "=SUM(R[3]C:R" & DerLig & "C)" Range("M3:S" & DerLig).Value = Range("M3:S" & DerLig).Value 'Remplacement des formules par les valeurs 'Format Range("M6:S" & DerLig).Interior.Color = RGB(238, 236, 225) Range("M6:S" & DerLig).Borders(xlEdgeLeft).Weight = xlMedium Range("M6:S" & DerLig).Borders(xlEdgeTop).Weight = xlMedium Range("M6:S" & DerLig).Borders(xlEdgeRight).Weight = xlMedium End Sub
Cdlt