XL 2010 Variable objet ou variable de bloc with non définie

[Micka_Plays]

Bonjour à tous,

Le message d'erreur suivant s'affiche : "variable objet ou variable de bloc with non définie"
J'ai vu plusieurs post qui en parle, mais je ne suis pas suffisemment pro pour comprendre pourquoi ça bug.

Si vous avez une idée, ça serait cool de votre part. Merci

Option Explicit

Private Sub UserForm_Initialize()
TXBPRE = Sheets("LISTES").Range("J3")
TXBNOM = Sheets("LISTES").Range("J4")
TXBDAT = Now()
TXBDAT = Format(TXBDAT, "dd/mm/yyyy")
TXBMA1 = Sheets("LISTES").Range("T2")
TXBMA2 = Sheets("LISTES").Range("T3")
TXBMA3 = Sheets("LISTES").Range("T4")
TXBMA4 = Sheets("LISTES").Range("T5")
TXBMA5 = Sheets("LISTES").Range("T6")
End Sub



Private Sub Retour_Click()
Unload Me 'Fermer ce UserForm
VBAProject.FRMRAS.Show 'Ouvrir le UserForm
End Sub


' Réfrences ******************************************************************
Private Sub CBBDU1_AfterUpdate()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBDU1.Value, LookIn:=xlValues, LookAt:=xlWhole)
End Sub
Private Sub CBBDU2_AfterUpdate()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBDU2.Value, LookIn:=xlValues, LookAt:=xlWhole)
End Sub
Private Sub CBBDU3_AfterUpdate()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBDU3.Value, LookIn:=xlValues, LookAt:=xlWhole)
End Sub
Private Sub CBBDU4_AfterUpdate()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBDU4.Value, LookIn:=xlValues, LookAt:=xlWhole)
End Sub
Private Sub CBBDU5_AfterUpdate()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBDU5.Value, LookIn:=xlValues, LookAt:=xlWhole)
End Sub
' ****************************************************************************

' Activités ******************************************************************
Private Sub CBBAC1_Change()
Dim CL As Variant
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBAC1.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("E" & CL.Row) = Me.CBBAC1.Value
End Sub
Private Sub CBBAC2_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBAC2.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("E" & CL.Row) = Me.CBBAC2.Value
End Sub
Private Sub CBBAC3_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBAC3.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("E" & CL.Row) = Me.CBBAC3.Value
End Sub
Private Sub CBBAC4_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBAC4.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("E" & CL.Row) = Me.CBBAC4.Value
End Sub
Private Sub CBBAC5_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBAC5.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("E" & CL.Row) = Me.CBBAC5.Value
End Sub
' ****************************************************************************

' Durée***********************************************************************
Private Sub CBBDU1_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBDU1.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("F" & CL.Row) = Me.CBBDU1.Value
CBBDU1 = Format(CBBDU1, "hh:mm")
End Sub
Private Sub CBBDU2_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBDU2.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("F" & CL.Row) = Me.CBBDU2.Value
CBBDU2 = Format(CBBDU2, "hh:mm")
End Sub
Private Sub CBBDU3_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBDU3.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("F" & CL.Row) = Me.CBBDU3.Value
CBBDU3 = Format(CBBDU3, "hh:mm")
End Sub
Private Sub CBBDUC4_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBDU4.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("F" & CL.Row) = Me.CBBDU4.Value
CBBDU4 = Format(CBBDU4, "hh:mm")
End Sub
Private Sub CBBDU5_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBDU5.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("F" & CL.Row) = Me.CBBDU5.Value
CBBDU5 = Format(CBBDU5, "hh:mm")
End Sub
' ****************************************************************************

' Nombre *********************************************************************
Private Sub TXBNB1_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.TXBNB1.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("G" & CL.Row) = Me.TXBNB1.Value
End Sub
Private Sub TXBNB2_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.TXBNB2.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("G" & CL.Row) = Me.TXBNB2.Value
End Sub
Private Sub TXBNB3_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.TXBNB3.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("G" & CL.Row) = Me.TXBNB3.Value
End Sub
Private Sub TXBNB4_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.TXBNB4.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("G" & CL.Row) = Me.TXBNB4.Value
End Sub
Private Sub TXBNB5_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.TXBNB5.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("G" & CL.Row) = Me.TXBNB5.Value
End Sub
' ****************************************************************************

 

[Hasco]

Bonjour,

Dans ces deux lignes:

Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBAC4.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("E" & CL.Row) = Me.CBBAC4.Value

La première cherche une cellule dans la colonne A (mais on ne sait pas sur quelle feuille) comportant la valeur de CBBAC4 (Par exemple AA4)
Comme elle ne trouve rien et ne renvoie rien (CL = Nothing) la seconde ligne lève une erreur puisque CL.Row ne peut pas exister puisque CL = Nothing.

Range(...) sans plus d'indication d'une feuille parent s'applique sur la feuille active du classeur actif.

J'écrirais plutôt quelque chose comme ça:

Private Sub CBBDUC4_Change()
    Dim CL As Range
    Dim Dlig As Integer
    '
    ' Mettre le nom de la feuille sur laquelle faire la recherche
    With Sheets("LaFeuilleSurLaQuelleOnCherche")
        Dlig = .Cells(Rows.Count, "A").End(xlUp).Row
        Set CL = .Range("A2:A" & Dlig).Find(What:=Me.CBBDU4.Value, LookIn:=xlValues, LookAt:=xlWhole)
        '
        If Not CL Is Nothing Then
            Sheets("TOUT").Range("F" & CL.Row) = Me.CBBDU4.Value
            CBBDU4 = Format(CBBDU4, "hh:mm")
        Else
            '
            ' Avertir l'utilisateur que la valeur n'a pas été trouvée
            ' ou faire tout autre action souhaitée
            MsgBox "La valeur '" & Me.CBBAC4.Value & "' n'a pas été trouvée sur la feuille '" & .Name & "'", vbExclamation, "Titre du message"

        End If
    End With
End Sub

Bonne fin d'après-midi