VBA-使用命名范围中的for循环将dd/mm/yyyy添加到递增的hh/mm单元格值中

Question

我是VBA的新手，所以请原谅这个问题，我正在编写一个代码，该代码将在命名范围内循环并添加dd/mm/yyyy，当单元格值超过0000时，dd将递增
我正在为if time1 < time2 then statement之后的行动而挣扎
具体说明如何在active cell中引用'dd/mm/yyyy'而不引用hh/mm。

任何关于如何做到这一点的更好逻辑的建议或看法！

Sub changeDate()
Dim rng As Range: Set rng = Sheets("Sheet3").Range("starttime")
Dim cel As Range
Dim time2 As String
Dim time1 As String
Sheets("Sheet3").Range("starttime").Activate
For Each cel In rng.Cells
cel.Select
time1 = ActiveCell.Value
time2 = ActiveCell.Offset(1, 0).Value
If time1 < time2 Then
'****  use time1 dd/mm/yyyy + time2 hh/mm for value of time2
End If
If time1 > time2 Then
'**** use (time1 dd/mm/yyyy + one day) + time2 hh/mm for value of time2
End If
Next cel
End Sub
' example of named range starttime 
'02/01/2018 07:25
'00/01/1900 12:28
'00/01/1900 17:38
'00/01/1900 01:01
'00/01/1900 08:07
'00/01/1900 13:22
'00/01/1900 18:33
'00/01/1900 01:45
'00/01/1900 08:47
'00/01/1900 14:20

Answer 1

您可以使用FIX((来获取任何数字的整数部分，在本例中是日期-时间值的日期部分。此外，你应该避免加速单元格，这会减慢代码的执行速度，你甚至不必将临时变量与你正在比较的值进行比较：

For Each cel In Rng.Cells
With cel
If .Value < .Offset(1, 0).Value Then
'****  use time1 dd/mm/yyyy + time2 hh/mm for value of time2
ElseIf .Value > Fix(.Offset(1, 0).Value) Then
'**** use (time1 dd/mm/yyyy + one day) + time2 hh/mm for value of time2
End If
End With
Next cel

干杯，Michal

Answer 2

这解决了我的问题

Sub datechange()
Dim rng As Range: Set rng = Sheets("Sheet3").Range("starttime")
Dim cel As Range
Dim day1 As Date
day1 = ("01/01/2018")
For Each cel In rng.Cells
With cel
If .Value > .Offset(1, 0).Value Then
cel = .Value + day1
day1 = day1 + 1
ElseIf .Value < (.Offset(1, 0).Value) Then
cel = .Value + day1
End If
End With

下一个cel

结束子