Bonjour le forum
le code ci dessous marchait très bien et maintenant il bug au niveau ActiveSheet.Paste avec le message d'erreur qui est dans le titre
J'ai regardé dasn le forum et sur le net mais impossible de trouver une solution
Merci de votre aide
Sub autoshape()
For n = ActiveSheet.Shapes.Count To 1 Step -1
If InStr(ActiveSheet.Shapes(n).Name, "Oval") <> 0 Or InStr(ActiveSheet.Shapes(n).Name, "AutoShape") <> 0 Then
ActiveSheet.Shapes(n).Delete
End If
Next n
colonnes = Array("B", "C", "E", "G", "I", "J")
For n = LBound(colonnes) To UBound(colonnes)
For m = 1 To 123
If Range(colonnes(n) & m) <> "" Then
If InStr(Range(colonnes(n) & m), "?") = 0 Then
'Set c = Sheets("Groupe semaine").Range("N3:U53").Find(Range(colonnes(n) & m), LookIn:=xlValues, lookat:=xlWhole)
Set c = Sheets("CODEDATE").Range("N3:W1000").Find(Range(colonnes(n) & m), LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
'MsgBox (c.Value & " " & c.Address)
Select Case c.Column
Case 14
Sheets("CODEDATE").Shapes("Oval 10").Copy
Case 15
Sheets("CODEDATE").Shapes("Oval 94").Copy
Case 16
Sheets("CODEDATE").Shapes("Oval 95").Copy
Case 17
Sheets("CODEDATE").Shapes("Oval 96").Copy
Case 18
Sheets("CODEDATE").Shapes("Oval 97").Copy
Case 19
Sheets("CODEDATE").Shapes("Oval 98").Copy
Case 20
Sheets("CODEDATE").Shapes("Oval 99").Copy
Case 21
Sheets("CODEDATE").Shapes("Oval 100").Copy
End Select
ActiveSheet.Paste
If c.Column < 22 Then
Selection.Top = Range(colonnes(n) & m).Top + 2
Selection.Left = Range(colonnes(n) & m).Left + (c.Column - 14) * 8 + 2
Else
'Selection.Top = Range(colonnes(n) & m + 1).Top - Sheets("Groupe semaine").Shapes("AutoShape 4").Height - 2
'Selection.Left = Range(colonnes(n) & m).Left + (c.Column - 18) * 26 + 2
End If
Set c = Sheets("CODEDATE").Cells.FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End If
End If
Next m
Next n
Range("A1").Select
End Sub
le code ci dessous marchait très bien et maintenant il bug au niveau ActiveSheet.Paste avec le message d'erreur qui est dans le titre
J'ai regardé dasn le forum et sur le net mais impossible de trouver une solution
Merci de votre aide
Sub autoshape()
For n = ActiveSheet.Shapes.Count To 1 Step -1
If InStr(ActiveSheet.Shapes(n).Name, "Oval") <> 0 Or InStr(ActiveSheet.Shapes(n).Name, "AutoShape") <> 0 Then
ActiveSheet.Shapes(n).Delete
End If
Next n
colonnes = Array("B", "C", "E", "G", "I", "J")
For n = LBound(colonnes) To UBound(colonnes)
For m = 1 To 123
If Range(colonnes(n) & m) <> "" Then
If InStr(Range(colonnes(n) & m), "?") = 0 Then
'Set c = Sheets("Groupe semaine").Range("N3:U53").Find(Range(colonnes(n) & m), LookIn:=xlValues, lookat:=xlWhole)
Set c = Sheets("CODEDATE").Range("N3:W1000").Find(Range(colonnes(n) & m), LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
'MsgBox (c.Value & " " & c.Address)
Select Case c.Column
Case 14
Sheets("CODEDATE").Shapes("Oval 10").Copy
Case 15
Sheets("CODEDATE").Shapes("Oval 94").Copy
Case 16
Sheets("CODEDATE").Shapes("Oval 95").Copy
Case 17
Sheets("CODEDATE").Shapes("Oval 96").Copy
Case 18
Sheets("CODEDATE").Shapes("Oval 97").Copy
Case 19
Sheets("CODEDATE").Shapes("Oval 98").Copy
Case 20
Sheets("CODEDATE").Shapes("Oval 99").Copy
Case 21
Sheets("CODEDATE").Shapes("Oval 100").Copy
End Select
ActiveSheet.Paste
If c.Column < 22 Then
Selection.Top = Range(colonnes(n) & m).Top + 2
Selection.Left = Range(colonnes(n) & m).Left + (c.Column - 14) * 8 + 2
Else
'Selection.Top = Range(colonnes(n) & m + 1).Top - Sheets("Groupe semaine").Shapes("AutoShape 4").Height - 2
'Selection.Left = Range(colonnes(n) & m).Left + (c.Column - 18) * 26 + 2
End If
Set c = Sheets("CODEDATE").Cells.FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End If
End If
Next m
Next n
Range("A1").Select
End Sub