Microsoft 365 Conversion feuille vide

[gabo29]

Bonjour,

J'ai une macro qui récupère des infos sur un onglet et dans lequel je dois convertir la date. Parfois, cet onglet est vide et la conversion fait planter la macro et l'empêche tourner. Je cherche à contourner ce problème, voici le code que j'utilise :

Sheets("feuil1").Select

For Each c In Range("L1:L" & Range("L" & Rows.Count).End(xlUp).Row)
If Not IsDate(c.Value) Then
c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2), Right(c.Value, 2))
End If
Next c

For Each c In Range("R1:R" & Range("R" & Rows.Count).End(xlUp).Row)
If Not IsDate(c.Value) Then
c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2), Right(c.Value, 2))
End If
Next c

For Each c In Range("P1😛" & Range("P" & Rows.Count).End(xlUp).Row)
If Not IsDate(c.Value) Then
c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2), Right(c.Value, 2))
End If
Next c

For Each c In Range("AG1:AG" & Range("AG" & Rows.Count).End(xlUp).Row)
If Not IsDate(c.Value) Then
c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2), Right(c.Value, 2))
End If
Next c

For Each c In Range("AH1:AH" & Range("AH" & Rows.Count).End(xlUp).Row)
If Not IsDate(c.Value) Then
c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2), Right(c.Value, 2))
End If
Next c

For Each c In Range("AB1:AB" & Range("AB" & Rows.Count).End(xlUp).Row)
If Not IsDate(c.Value) Then
c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2), Right(c.Value, 2))
End If
Next c

For Each c In Range("AC1:AC" & Range("AC" & Rows.Count).End(xlUp).Row)
If Not IsDate(c.Value) Then
c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2), Right(c.Value, 2))
End If
Next c

For Each c In Range("AD1:AD" & Range("AD" & Rows.Count).End(xlUp).Row)
If Not IsDate(c.Value) Then
c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2), Right(c.Value, 2))
End If
Next c

Pouvez vous m'aider svp ?
Merci d'avance,
Gabo29

 

[Robert]

Bonsoir Gabo, bonsoir le forum,

Essaie comme ça (à continuer sur toutes les plages...) :

Sheets("feuil1").Select
For Each c In Range("L1:L" & Range("L" & Rows.Count).End(xlUp).Row)
If c.Value <> "" Then
    If Not IsDate(c.Value) Then c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2), Right(c.Value, 2))
End If
Next c

For Each c In Range("R1:R" & Range("R" & Rows.Count).End(xlUp).Row)
If c.Value <> "" Then
    If Not IsDate(c.Value) Then c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2), Right(c.Value, 2))
End If
Next c

 

[Patrice33740]

Bonjour,

Remplace :

 If Not IsDate(c.Value) Then

Par

  If Not IsDate(c.Value) And Len(c.Text) = 8 And Val(c.Value) >= 19000101 Then

 

[gabo29]

Bonsoir Robert, Bonsoir le forum,

C'est parfait, merci beaucoup.
Cdt,