Erreur 9 dans ma macro

Hello,

Dans ma macro ci-dessous j'ai une erreur 9 dès mon premier if :

    If Worksheets("demande_cites").Range("AA" & i).Value = "Non diffusée" Then
    Worksheets("demande_cites").Range("R" & j).Value = i
    j = j + 1
    End If

Il y a peut-être d'autres erreurs au sein de mon code, mais j'ai beau le retourner dans tous les sens, cela dépasse mes compétences !

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' envoi un mail automatique avec le contenu de l'anomalie
' Consignes de migration: rajouter un statut "Non diffusée" dans la dernière colonne du tableau
' Donner accès à Excel 2010
' Intégrer la liste de diffusion

Dim ol As Object, monItem As Object
Dim i
Dim j
Dim k
Dim cont
Dim Tracking
Dim Cites
Dim jour

i = 2
j = 2
te = 0
k = 2

' On récupère la ou les lignes concernées par un éventuel envoi
For i = 2 To 2000

    If Worksheets("demande_cites").Range("AA" & i).Value = "Non diffusée" Then
    Worksheets("demande_cites").Range("R" & j).Value = i
    j = j + 1
    End If

Next i

' On envoie les mails concernés

While Worksheets("demande_cites").Range("R" & k).Value <> ""
    cont = CInt(Worksheets("demande_cites").Range("R" & k).Value)
    'On récupère les données
    Tracking = Worksheets("demande_cites").Range("A" & cont).Value
    Cites = Worksheets("demande_cites").Range("Y" & cont).Value
    jour = Worksheets("demande_cites").Range("Z" & cont).Value

    'On envoit le mail en question
    Set ol = CreateObject("outlook.application")
    Set monItem = ol.CreateItem(olMailItem)
    'monItem.To = " mon@adresse.com "
    monItem.To = " mon@adresse.com "
    monItem.Cc = " mon@adresse.com "
    If Cites = "B" Then
    monItem.Subject = "Demande de CITES non faite - Tracking n°" & Tracking
    monItem.Body = "Bonjour," & Chr(13) & Chr(13) & "Pour le tracking n°" & Tracking & "la demande de CITES n'a pas été effectuée." & Chr(13) & Chr(13) & "Il reste " & jour & "avant la date maximale demandée."
    Else
    If Cites = "C" Then
    monItem.Subject = "CITES non reçu du ministère - Tracking n°" & Tracking
    monItem.Body = "Bonjour," & Chr(13) & Chr(13) & "Pour le tracking n°" & Tracking & "la demande de CITES n'a pas encore eu de réponse du ministère." & "Il reste " & jour & "avant la date maximale demandée."
    Else
    End If
    End If
    monItem.Send
    Set ol = Nothing
    k = k + 1

Wend

Worksheets("demande_cites").Range("R2:R40").ClearContents

End Sub

Merci d'avance de vos lumières

Bonjour,

tous semble correct, vérifier si l'onglet "demande_cites" exist, des fois qu'il y aurait un espace dans le nom

Sub test()
Worksheets("demande_cites").Activate
End Sub

Bonsoir,

Pas beaucoup d'effort pour typer tes variables !

Et tu initialises i... pour changer d'avis et l'utiliser comme compteur de boucles !

Bon ! Tout cela n'est pas de bonne qualité (comme la boucle de 2 à 2000, un forfait !) mais n'entraîne pas d'erreur.

Le seul élément à ce stade susceptible de déclencher un erreur 9 est le nom de la feuille qu'il convient donc de vérifier...

Cordialement.

Bonjour,

Comme le dit MFerrand, que je salut au passage ainsi que sabV, c'est le nom de ta feuille qui est mal orthographié par rapport à l'onglet !

J'ai juste un peu retouché ton code mais il est possible de l'améliorer encore :

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    ' envoi un mail automatique avec le contenu de l'anomalie
    ' Consignes de migration: rajouter un statut "Non diffusée" dans la dernière colonne du tableau
    ' Donner accès à Excel 2010
    ' Intégrer la liste de diffusion

    Dim ol As Object, monItem As Object
    Dim DerLigne As Long
    Dim I As Long
    Dim Tracking As String
    Dim Cites As String
    Dim jour As String

    ' On envoie les mails concernés
    DerLigne = Worksheets("demande_cites").Cells(Rows.Count, 27).End(xlUp).Row 'sur colonne AA

    For I = 2 To DerLigne

        If Worksheets("demande_cites").Range("AA" & I).Value = "Non diffusée" Then

            'On récupère les données
            Tracking = Worksheets("demande_cites").Range("A" & I).Value
            Cites = Worksheets("demande_cites").Range("Y" & I).Value
            jour = Worksheets("demande_cites").Range("Z" & I).Value

            'On envoit le mail en question
            Set ol = CreateObject("Outlook.Application")
            Set monItem = ol.CreateItem(0)

            With monItem

                .To = "mon@adresse.com;mon@adresse.com"
                .Cc = "mon@adresse.com;mon@adresse.com"

                If Cites = "B" Then

                    .Subject = "Demande de CITES non faite - Tracking n°" & Tracking

                    .Body = "Bonjour," & _
                            Chr(13) & Chr(13) & _
                            "Pour le tracking n° " & _
                            Tracking & _
                            " la demande de CITES n'a pas été effectuée." & _
                            Chr(13) & Chr(13) & _
                            "Il reste " & jour & " avant la date maximale demandée."

                ElseIf Cites = "C" Then

                    .Subject = "CITES non reçu du ministère - Tracking n°" & Tracking

                    .Body = "Bonjour," & _
                            Chr(13) & Chr(13) & _
                            "Pour le tracking n° " & _
                            Tracking & _
                            " la demande de CITES n'a pas encore eu de réponse du ministère." & _
                            Chr(13) & Chr(13) & _
                            "Il reste " & jour & " avant la date maximale demandée."
                End If

                '.Display 'pour le contrôle si besoin !   

                .Send

            End With

            Set monItem = Nothing
            Set ol = Nothing

        End If

    Next I

    Worksheets("demande_cites").Range("R2:R40").ClearContents

End Sub

Suuiiiis-je bêêêêête ! C'est réglé, problème de nom de mon classeur effectivement ... Merci pour l'aide sur l'allégement du fichier !