[已解决]时间累加取整数

一成不变变

请帮忙，当前时间与Range("B" & m)相减乘以200，代码怎样写，请帮忙


Public Sub 时间()
z = Columns("A").Find("*", , , , , xlPrevious).Row
For m = 2 To z

   If Range("A" & m) < Range("B" & m) Then
          h = (Range("B" & m) - Range("A" & m)) * 24
       Else
          h = (Range("B" & m) - Range("A" & m)) * 24 + 24
    End If
    i = Application.WorksheetFunction.Ceiling(h, 0.5)
    Range("C" & m) = i * 100

    bj = (Application.WorksheetFunction.Text(Now - Range("B" & m), "[m]"))
    Range("D" & m) = (Application.WorksheetFunction.Ceiling(bj, 0.5)) * 200

      Next
End Sub

最佳答案

月排行榜 / 总排行榜

grf1973

2015-12-15 16:28

按你题意，附加费用应该是小时数*200.

1. Sub grf()
   
2.     arr = [a1].CurrentRegion
   
3.     For i = 2 To UBound(arr)
   
4.         t1 = arr(i, 1): t2 = arr(i, 2): t = Time
   
5.         h1 = IIf(t2 > t1, t2 - t1, t2 - t1 + 1) * 24
   
6.         h2 = IIf(t > t2, t - t2, t - t2 + 1) * 24
   
7.         arr(i, 3) = Application.WorksheetFunction.Ceiling(h1, 0.5) * 100
   
8.         arr(i, 4) = Application.WorksheetFunction.Ceiling(h2, 0.5) * 200
   
9.     Next
   
10.     [a1].CurrentRegion = arr
    
11. End Sub
    

复制代码

跳转到最佳答案楼层

爱疯

看不懂题意，手动写下c2,d2,e2是啥？

grf1973

你的代码改成这样是否正确：bj = (Application.WorksheetFunction.Text(Time - Range("B" & m), "[h]"))
重新编了一个代码，看看是否正确：

1. Sub grf()
   
2.     arr = [a1].CurrentRegion
   
3.     For i = 2 To UBound(arr)
   
4.         t1 = arr(i, 1): t2 = arr(i, 2): t = Time
   
5.         h1 = IIf(t2 > t1, t2 - t1, t2 - t1 + 1) * 24
   
6.         h2 = IIf(t > t2, t - t2, t - t2 + 1) * 24
   
7.         arr(i, 3) = Application.WorksheetFunction.Ceiling(h1, 0.5) * 100
   
8.         arr(i, 4) = Application.WorksheetFunction.Ceiling(h2, 0.5) * 100
   
9.     Next
   
10.     [a1].CurrentRegion = arr
    
11. End Sub

复制代码

grf1973

按你题意，附加费用应该是小时数*200.

1. Sub grf()
   
2.     arr = [a1].CurrentRegion
   
3.     For i = 2 To UBound(arr)
   
4.         t1 = arr(i, 1): t2 = arr(i, 2): t = Time
   
5.         h1 = IIf(t2 > t1, t2 - t1, t2 - t1 + 1) * 24
   
6.         h2 = IIf(t > t2, t - t2, t - t2 + 1) * 24
   
7.         arr(i, 3) = Application.WorksheetFunction.Ceiling(h1, 0.5) * 100
   
8.         arr(i, 4) = Application.WorksheetFunction.Ceiling(h2, 0.5) * 200
   
9.     Next
   
10.     [a1].CurrentRegion = arr
    
11. End Sub
    

复制代码