如何用Application.WorksheetFunction添加循环？
如何用Application.WorksheetFunction添加循环？
提问于 2021-05-20 04:31:50
你能告诉我如何循环我的代码通过所有填充的行(基于行D)吗？我需要从ad2中减去d2，从ad3中减去d3，依此类推，并将结果放入ae列(我猜是偏移量)。
理想情况下，避免在ae中输入公式，而是使用Application.WorksheetFunction.Value=Total。
Sub valuedifference()
Dim Total As Double
Dim TimeX As Date
Dim TimeY As Date
With ThisWorkbook.Sheets("Test1")
TimeX = CDate(Range("d2").Value)
TimeY = CDate(Range("ad2").Value)
Total = TimeValue(TimeY) - TimeValue(TimeX)
Range("ag2").Value = Abs(Total * 24)
Range("ah2").Value = Abs(Total * 1440)
End With
End Sub回答 2
Stack Overflow用户
回答已采纳
发布于 2021-05-20 04:54:45
下面的宏使用列D查找最后一行，然后循环每一行，并将结果放在列AE...
Sub valuedifference()
Dim Total As Double
Dim TimeX As Date
Dim TimeY As Date
Dim LastRow As Long
Dim i As Long
With ThisWorkbook.Sheets("Test1")
LastRow = .Cells(.Rows.Count, "d").End(xlUp).Row
For i = 2 To LastRow
TimeX = CDate(.Range("d" & i).Value)
TimeY = CDate(.Range("ad" & i).Value)
Total = DateDiff("n", TimeY, TimeX)
.Range("AE" & i).Value = Total
.Range("AG" & i).Value = Format(Abs(Total), "#.##")
.Range("AH" & i).Value = Format(Abs(Total), "#.##")
Next i
End With
End SubStack Overflow用户
发布于 2021-05-20 05:40:19
我强烈建议使用Range变量和偏移量，而不是组装单元格名称引用。
由于回写电子表格是一个包含3个单元格的块，因此可以使用所需值的Array来写入该块，以减少电子表格的更新。
对我来说，一个悬而未决的问题是，你是也想捕捉天数的差异，还是像这里一样只捕捉时间差(不管日期)？
Sub valuedifference()
Dim Total As Double
Dim TimeX As Date
Dim TimeY As Date
Dim LastD As Range
Dim DRange As Range
Dim ACell As Range
Set LastD = Sheets("Test1").Cells(Sheets("Test1").Cells.Rows.Count, 4).End(xlUp)
Set DRange = Range(Sheets("Test1").Range("D2"), LastD)
For Each ACell In DRange
TimeX = CDate(ACell.Value) ' from D column
TimeY = CDate(ACell.Offset(0, 26).Value) ' from AD column
Total = TimeValue(TimeY) - TimeValue(TimeX)
' Place results in AE rightward cells
ACell.Offset(0, 27).Resize(1, 3).Value _
= Array(Total, Abs(Total * 24), Abs(Total * 1440))
Next ACell
End Sub页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/67610486
复制相关文章
相似问题
腾讯云开发者
