Macro erreur d'éxecution 1004

[QLLEFEVRE]

Bonjour,

j'ai souhaite faire une macro afin de répéter la même tâche sur plusieurs feuilles du classeur, avec des mises en formes conditionnelles et des mises en formes simple.

Cependant, lorsque je souhaite exécuter la macro, un message pop-up affiche : "erreur d’exécution 1004, erreur définie par l'application ou par l'objet". J'ai donc la possibilité de faire un débogage mais je n'y connais pas grand chose.

Sur Visual basic, la deuxième ligne du code est en jaune fluo, certainement car c'est la ou est l'erreur.

Voici la ligne en fluo : ActiveCell.Offset(-8, 0).Range("A1").Activate

et voici ci dessous le code dans sa totalité :

Sub QL()
'
' QL Macro
'

'
ActiveCell.Columns("A:B").EntireColumn.Select
ActiveCell.Offset(-8, 0).Range("A1").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Distributeur"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "Statut abnt"
ActiveCell.Offset(1, -1).Range("A1:A35").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Légende!$E$2:$E$3"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
ActiveCell.Offset(0, 1).Range("A1:A35").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Légende!$F$2:$F$5"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
ActiveCell.Offset(0, -1).Range("A1:A35").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="MF", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveCell.Offset(0, 1).Range("A1:A35").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="Nouveaux", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="Livré", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="Résilié", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.349986266670736
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveCell.Offset(-4, -1).Range("A1:L2").Select
ActiveCell.Offset(-4, 1).Range("A1").Activate
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveCell.Offset(3, 8).Range("A136").Select
Cells.FormatConditions.Delete
ActiveCell.Range("A136").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NBCAR(SUPPRESPACE(I4))=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.499984740745262
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveCell.Offset(1, -8).Range("A1:A35").Select
ActiveCell.Activate
Selection.FormatConditions.Add Type:=xlTextString, String:="MF", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveCell.Offset(1, -7).Range("A1:A35").Select
ActiveCell.Activate
Selection.FormatConditions.Add Type:=xlTextString, String:="Nouveaux", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveCell.Offset(1, -7).Range("A1:A35").Select
ActiveCell.Activate
Selection.FormatConditions.Add Type:=xlTextString, String:="Livré", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveCell.Offset(1, -7).Range("A1:A35").Select
ActiveCell.Activate
Selection.FormatConditions.Add Type:=xlTextString, String:="Résilié", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.349986266670736
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveCell.Offset(31, 8).Range("A1").Select
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveCell.Offset(-30, -8).Range("A1:A35").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Légende!$D$2:$D$13"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveCell.Offset(-1, -8).Range("A1:C36").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End Sub



Merci d'avance pour votre aide,

Quentin

 

[jp14]

Re : Macro erreur d'éxecution 1004

Bonjour et bienvenue sur le forum

ActiveCell.Offset(-8, 0).Range("A1").Activate
Si la cellule active est dans la ligne 1 l'offset avec -8 donne une une ligne qui n'existe pas.

JP

 

[tototiti2008]

Re : Macro erreur d'éxecution 1004

Bonjour QLLEFEVRE,

Comme tout ton code dépend de Activecell dès le départ, tout dépend de la position de la cellule active quand tu lance la macro...

Mais difficile de t'en dire plus sans savoir : sur quoi agit ta macro (fichier exemple ?)

Edit : Bonjour JP