XL 2013 SUPPRESSION DES #DIV/0

[Claude5]

Bonjour,

dans ma macro j'ai cette formule ActiveCell.FormulaR1C1 = "=+(RC[-1]/RC[-2])-1" mais je ne sais pas écrire la formule me permettant d'afficher un - si le % est impossible, en utilisant le sierreur.

Merci/Claude


Sub Macro1()
'
' Macro1 Macro
' Macro enregistrée le 10/11/2011 par claude
' Macro modifiée le 16/11/2012 par Claude, basculement en Excel 2010

'
Windows("DEADLINES_SUMMARY V2.xlsx").Activate
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Columns("L:L").Select
Selection.Insert Shift:=xlToRight
Columns("N:N").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=5
Columns("Q:Q").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.LargeScroll ToRight:=-1
Range("G2").Select
ActiveCell.FormulaR1C1 = "=+(RC[-1]/RC[-2])-1"
Range("G2").Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.0%"
Selection.NumberFormat = "0.00%"
Selection.AutoFill Destination:=Range("G2:G382"), Type:=xlFillDefault
Range("G2:G382").Select
ActiveWindow.ScrollRow = 355
ActiveWindow.ScrollRow = 353
ActiveWindow.ScrollRow = 352
ActiveWindow.ScrollRow = 349
ActiveWindow.ScrollRow = 346
ActiveWindow.ScrollRow = 342
ActiveWindow.ScrollRow = 338
ActiveWindow.ScrollRow = 331
ActiveWindow.ScrollRow = 326
ActiveWindow.ScrollRow = 319
ActiveWindow.ScrollRow = 313
ActiveWindow.ScrollRow = 304
ActiveWindow.ScrollRow = 296
ActiveWindow.ScrollRow = 288
ActiveWindow.ScrollRow = 279
ActiveWindow.ScrollRow = 271
ActiveWindow.ScrollRow = 261
ActiveWindow.ScrollRow = 252
ActiveWindow.ScrollRow = 243
ActiveWindow.ScrollRow = 235
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 215
ActiveWindow.ScrollRow = 207
ActiveWindow.ScrollRow = 196
ActiveWindow.ScrollRow = 187
ActiveWindow.ScrollRow = 176
ActiveWindow.ScrollRow = 168
ActiveWindow.ScrollRow = 161
ActiveWindow.ScrollRow = 154
ActiveWindow.ScrollRow = 148
ActiveWindow.ScrollRow = 142
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 132
ActiveWindow.ScrollRow = 126
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 111
ActiveWindow.ScrollRow = 108
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 90
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("I2").Select
ActiveCell.FormulaR1C1 = "=+(RC[-1]/RC[-4])-1"
Range("I2").Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.0%"
Selection.NumberFormat = "0.00%"
Selection.AutoFill Destination:=Range("I2:I382"), Type:=xlFillDefault
Range("I2:I382").Select
Range("L2").Select
ActiveCell.FormulaR1C1 = "=+(RC[-1]/RC[-2])-1"
Range("L2").Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.0%"
Selection.NumberFormat = "0.00%"
Selection.AutoFill Destination:=Range("L2:L383"), Type:=xlFillDefault
Range("L2:L383").Select
Range("A1").Select
ActiveWindow.SmallScroll ToRight:=5
Range("N2").Select
ActiveCell.FormulaR1C1 = "=+(RC[-1]/RC[-4])-1"
Range("N2").Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.0%"
Selection.NumberFormat = "0.00%"
Selection.AutoFill Destination:=Range("N2:N383"), Type:=xlFillDefault
Range("N2:N383").Select
Range("A1").Select
ActiveWindow.SmallScroll ToRight:=9
Range("Q2").Select
ActiveCell.FormulaR1C1 = "=+(RC[-1]/RC[-2])-1"
Range("Q2").Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.0%"
Selection.NumberFormat = "0.00%"
Selection.AutoFill Destination:=Range("Q2:Q383"), Type:=xlFillDefault
Range("Q2:Q383").Select
Range("A1").Select
ActiveWindow.SmallScroll ToRight:=9
Range("S2").Select
ActiveCell.FormulaR1C1 = "=+(RC[-1]/RC[-4])-1"
Range("S2").Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.0%"
Selection.NumberFormat = "0.00%"
Selection.AutoFill Destination:=Range("S2:S383"), Type:=xlFillDefault
Range("S2:S383").Select
Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Cells.EntireColumn.AutoFit
Range("A1").Select
ActiveWorkbook.SaveAs Filename:= _
"C:\TEMP\DEADLINES_APT V4.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
Range("A1").Select
ChDir "C:\TEMP"
ActiveWorkbook.Save

End Sub

 

[M12]

Bonjour,

Je pense fortement que tu ferais mieux de placer un classeur modèle avec tes demandes, car ta macro enregistré est un peu (beaucoup) à réduire fortement par du plus simple
Ce n'est que mon avis

 

[Claude5]

J'ai crée cette macro par enregistrement de mon travail et il est vrai que je "tatonais" un peu, d'où des enregistrements inutiles, style ActiveWindow.ScrollRow = XXX