27.11.2023, 21:27 (Dieser Beitrag wurde zuletzt bearbeitet: 27.11.2023, 21:42 von snb.)
Oder
Code:
Sub M_snb()
With CreateObject("ADODB.recordset")
.Open "SELECT '=' & replace(replace(replace(replace(replace(tijd,""d"",""""),""h"",""/24""),""m"",""/1440""),""s"",""/86400""),"" "",""+"") FROM `time$`", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0"""
Cells(20, 2).CopyFromRecordset .DataSource
End With
Cells(20, 2).CurrentRegion = Cells(20, 2).CurrentRegion.Value
End Sub
Und was PQ bei @PIVPQ macht ist nur dieses mit einer anderen Sprache:
Code:
Sub M_snb()
With CreateObject("ADODB.recordset")
.Open "SELECT left(tijd,instr(tijd,""d"")), right(left(tijd,instr(tijd,""h"")),3) ,right(left(tijd,instr(tijd,""m"")),3), right(left(tijd,instr(tijd,""s"")),3) FROM `time$`", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0"""
sheets("time").Cells(4, 2).CopyFromRecordset .DataSource
End With
End Sub
28.11.2023, 09:04 (Dieser Beitrag wurde zuletzt bearbeitet: 28.11.2023, 09:05 von Fennek.)
Hallo,
eine Alternative: Der Code berechnet die Zeit ([h]:mm:ss)
Code:
Sub T_1()
Dim Ar, Zt As Date
For i = 1 To 7
Ar = Split(Cells(i, 1))
For a = 0 To UBound(Ar)
Select Case Right(Ar(a), 1)
Case Is = "d": Zt = Val(Ar(a))
Case Is = "h": Zt = Zt + Val(Ar(a)) / 24
Case Is = "m": Zt = Zt + Val(Ar(a)) / 1440
Case Is = "s": Zt = Zt + Val(Ar(a)) / 86400
Case Else: Stop
End Select
Next a
Cells(i, 2) = Zt
Zt = 0
Next i
End Sub