vba : erreur d'exécution 1004

Version imprimable

1 pièce(s) jointe(s)

bonjour,

j'ai un code tout simple mais qui me genere l'erreur suivante:

erreur d'execution 1004
erreur definie par l'application ou par l'objet

voici mon code:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Private Sub launch_Click() Set Dico_old_phase = CreateObject("Scripting.dictionary") Set Dico_new_phase = CreateObject("Scripting.dictionary") For Each c In ThisWorkbook.Worksheets("oldfollowup").Range("a2", [a65000].End(xlUp)) Dico_old_phase.Add c.Value, c.Offset(0, 8).Value Next c For Each d In ActiveWorkbook.Sheets("newfollowup").Range("a2", [a65000].End(xlUp)) Dico_new_phase.Add d.Value, d.Offset(0, 8).Value Next d MsgBox Dico_old_phase.Item("6832") & ";" & Dico_new_phase.Item("6700") End Sub
l'erreur provient de la ligne :

Code:

For Each c In ThisWorkbook.Worksheets("oldfollowup").Range("a2", [a65000].End(xlUp))

j'ai tout essayé : enlever les caractères speciaux, les references, changer le code -> toujours la meme erreur!!! ca me desespere :cry:

en pj le fichier excel que j'utilise

Merci d'avance pour votre aide..

Bonjour,

Essaie comme cela

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Option Explicit
Private Sub launch_Click()
Dim Dico_old_phase, Dico_new_phase
Dim C As Range, Mot As Range
 
    Set Dico_old_phase = CreateObject("Scripting.dictionary")
    Set Dico_new_phase = CreateObject("Scripting.dictionary")
 
    With ThisWorkbook.Worksheets("oldfollowup")
        For Each C In .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp))
            If Not Dico_old_phase.Exists(C.Value) Then Dico_old_phase.Add C.Value, C.Offset(0, 2).Value
        Next C
    End With
 
    With ActiveWorkbook.Sheets("newfollowup")
        For Each Mot In .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp))
            If Not Dico_new_phase.Exists(Mot.Value) Then Dico_new_phase.Add Mot.Value, Mot.Offset(0, 2).Value
        Next Mot
    End With
 
   MsgBox Dico_old_phase.Item(6832) & " ; " & Dico_new_phase.Item(6700)
End Sub

Cordialement.

20/12/2013, 10h08
rayba89

Bonjour gFZT82,

Merci ca marche parfaitement! :)