[已解决]修改时间数据后 好像单元格属性发生变化了，再跑一遍代码 计算出不同结果该怎么办

mathking77

E列和F列是表示时间的数据，
我用代码跑完后 计算出来差值 结果显示在H列，运行代码算出来的结果是正确的 没有问题。

但如果我修改下E列或者F列的时间，计算出来的结果会发生错误，有的直接计算不出结果了
比如 7:20 我还是改成7:20， 再运行一遍代码， 发现计算出来的结果和之前的不一样了，同样的7:20，结果却不同了

我如果直接复制其他行的时间过去 计算出来也是对的，所以说复制其他格时间是不会发生错误的，只有手工修改了时间会发生错误，
所以我觉得是改完时间后的格子属性发生了变化，没有修改的时候 时间数据是显示XX:XX, 一修改就会变成XX:XX:XX

求大神指点下 ，应该怎么解决这个问题，
需要修改完时间数据后 再运行代码 算出来也要是正确的

最佳答案

月排行榜 / 总排行榜

雪舞子

2016-7-3 18:37

代码太长，不了解你的意图，所以无从分析和修改。

初步看了一下你的表格，好像数据是从哪导出来的，
几列时间数据格式不一致
C列数据为自定义时间格式，其他几列时间数据是文本格式，
代码中用了 TimeValue() 函数去转换和比较时间，
TimeValue() 函数参数要求是文本格式，
因此在比较和计算C列时间数据时会出现错误，
同样道理，你更改了其他列的时间数据，
excel会自动将原来的文本格式转换成自定义的时间格式，
这样经过TimeValue()函数转换后自然也会出现错误或结果不正确。

原代码作者使用On Error Resume Next
略过了大量“类型不正确”错误以及其他错误，
因此结果有差异在所难免，
最好找原代码编写者重新优化一下代码。

初步给你个建议：
将代码中所有TimeValue()函数用CDate()替换一下，
测试一下结果是不是能达到你的要求。

跳转到最佳答案楼层

mathking77

mathking77

求大神解答下

mathking77

雪舞子

代码太长，不了解你的意图，所以无从分析和修改。

初步看了一下你的表格，好像数据是从哪导出来的，
几列时间数据格式不一致
C列数据为自定义时间格式，其他几列时间数据是文本格式，
代码中用了 TimeValue() 函数去转换和比较时间，
TimeValue() 函数参数要求是文本格式，
因此在比较和计算C列时间数据时会出现错误，
同样道理，你更改了其他列的时间数据，
excel会自动将原来的文本格式转换成自定义的时间格式，
这样经过TimeValue()函数转换后自然也会出现错误或结果不正确。

原代码作者使用On Error Resume Next
略过了大量“类型不正确”错误以及其他错误，
因此结果有差异在所难免，
最好找原代码编写者重新优化一下代码。

初步给你个建议：
将代码中所有TimeValue()函数用CDate()替换一下，
测试一下结果是不是能达到你的要求。

mathking77

雪舞子 发表于 2016-7-3 18:37
代码太长，不了解你的意图，所以无从分析和修改。

初步看了一下你的表格，好像数据是从哪导出来的，

因为数据表格是从打卡软件直接导出来的 所以最初就是文本格式   我之后修改完就自动变成自定义格式的话 如果我在代码中加上一句 把EF两列所有单元格设置成文本格式 那就不用改掉timevalue（）了吧 因为计算只用到了E-F的数据 C-D列的数据是不需要的
请问把EF列设置成文本格式的代码是什么？ 我加进去就行了

wanao2008

1. Sub xx()
   
2. 
   
3.     Dim arr, brr(), n&, i&, dA, dB, dC, dD, dE, dF, dG, dH, dI, dJ, j&
   
4.     With Sheet1
   
5.         n = .Cells(.Rows.Count, 1).End(xlUp).Row
   
6.         arr = .Range("C2:F" & n)
   
7.         On Error Resume Next
   
8.         For i = 1 To n - 1
   
9.             ReDim Preserve brr(1 To 3, 1 To i)
   
10.             If arr(i, 3) <> "" And arr(i, 4) <> "" Then
    
11.                     If TimeValue(CDate(arr(i, 3))) <= TimeValue("7:30:00") And TimeValue(CDate(arr(i, 4))) > TimeValue("7:30:00") And TimeValue(CDate(arr(i, 4))) < TimeValue("11:00:00") Then
    
12.                            brr(1, i) = 取值((TimeValue(CDate(arr(i, 4))) - TimeValue("7:30:00")) * 24)
    
13.                     ElseIf TimeValue(CDate(arr(i, 3))) <= TimeValue("7:30:00") And TimeValue(CDate(arr(i, 4))) >= TimeValue("11:00:00") And TimeValue(CDate(arr(i, 4))) <= TimeValue("12:00:00") Then
    
14.                            brr(1, i) = 取值(3.5)
    
15.                     ElseIf TimeValue(CDate(arr(i, 3))) <= TimeValue("7:30:00") And TimeValue(CDate(arr(i, 4))) > TimeValue("12:00:00") And TimeValue(CDate(arr(i, 4))) < TimeValue("16:30:00") Then
    
16.                            brr(1, i) = 取值(3.5 + (TimeValue(CDate(arr(i, 4))) - TimeValue("12:00:00")) * 24)
    
17.                     ElseIf TimeValue(CDate(arr(i, 3))) <= TimeValue("7:30:00") And TimeValue(CDate(arr(i, 4))) >= TimeValue("16:30:00") Then
    
18.                            brr(1, i) = 取值(8)
    
19.                     ElseIf TimeValue(CDate(arr(i, 3))) > TimeValue("7:30:00") And TimeValue(CDate(arr(i, 3))) < TimeValue("11:00:00") And TimeValue(CDate(arr(i, 4))) > TimeValue("7:30:00") And TimeValue(CDate(arr(i, 4))) < TimeValue("11:00:00") Then
    
20.                            brr(1, i) = 取值((TimeValue(CDate(arr(i, 4))) - TimeValue(CDate(arr(i, 3)))) * 24)
    
21.                     ElseIf TimeValue(CDate(arr(i, 3))) > TimeValue("7:30:00") And TimeValue(CDate(arr(i, 3))) < TimeValue("11:00:00") And TimeValue(CDate(arr(i, 4))) >= TimeValue("11:00:00") And TimeValue(CDate(arr(i, 4))) <= TimeValue("12:00:00") Then
    
22.                            brr(1, i) = 取值((TimeValue("11:00:00") - TimeValue(CDate(arr(i, 3)))) * 24)
    
23.                     ElseIf TimeValue(CDate(arr(i, 3))) > TimeValue("7:30:00") And TimeValue(CDate(arr(i, 3))) < TimeValue("11:00:00") And TimeValue(CDate(arr(i, 4))) > TimeValue("12:00:00") And TimeValue(CDate(arr(i, 4))) < TimeValue("16:30:00") Then
    
24.                            brr(1, i) = 取值((TimeValue("11:00:00") - TimeValue(CDate(arr(i, 3))) + TimeValue(CDate(arr(i, 4))) - TimeValue("12:00:00")) * 24)
    
25.                     ElseIf TimeValue(CDate(arr(i, 3))) > TimeValue("7:30:00") And TimeValue(CDate(arr(i, 3))) < TimeValue("11:00:00") And TimeValue(CDate(arr(i, 4))) >= TimeValue("16:30:00") Then
    
26.                            brr(1, i) = 取值((TimeValue("11:00:00") - TimeValue(CDate(arr(i, 3)))) * 24 + 4.5)
    
27.                     ElseIf TimeValue(CDate(arr(i, 3))) >= TimeValue("11:00:00") And TimeValue(CDate(arr(i, 3))) <= TimeValue("12:00:00") And TimeValue(CDate(arr(i, 4))) > TimeValue("12:00:00") And TimeValue(CDate(arr(i, 4))) < TimeValue("16:30:00") Then
    
28.                            brr(1, i) = 取值((TimeValue(CDate(arr(i, 4))) - TimeValue("12:00:00")) * 24)
    
29.                     ElseIf TimeValue(CDate(arr(i, 3))) >= TimeValue("11:00:00") And TimeValue(CDate(arr(i, 3))) <= TimeValue("12:00:00") And TimeValue(CDate(arr(i, 4))) >= TimeValue("16:30:00") Then
    
30.                            brr(1, i) = 取值(4.5)
    
31.                     ElseIf TimeValue(CDate(arr(i, 3))) > TimeValue("12:00:00") And TimeValue(CDate(arr(i, 3))) < TimeValue("16:30:00") And TimeValue(CDate(arr(i, 4))) > TimeValue("12:00:00") And TimeValue(CDate(arr(i, 4))) < TimeValue("16:30:00") Then
    
32.                            brr(1, i) = 取值((TimeValue(CDate(arr(i, 4))) - TimeValue(CDate(arr(i, 3)))) * 24)
    
33.                     ElseIf TimeValue(CDate(arr(i, 3))) > TimeValue("12:00:00") And TimeValue(CDate(arr(i, 3))) < TimeValue("16:30:00") And TimeValue(CDate(arr(i, 4))) >= TimeValue("16:30:00") Then
    
34.                            brr(1, i) = 取值((TimeValue("16:30:00") - TimeValue(CDate(arr(i, 3)))) * 24)
    
35. 
    
36.                     Else
    
37.                            brr(1, i) = ""
    
38.                     End If
    
39.                               
    
40.                 If TimeValue(CDate(arr(i, 4))) >= TimeValue("17:20:00") Then brr(2, i) = 取值((TimeValue(CDate(arr(i, 4))) - TimeValue("17:00:00")) * 24)
    
41.                 If brr(2, i) >= 3 Then brr(3, i) = 1
    
42. 
    
43.           End If
    
44. 
    
45.         Next
    
46.         .Range("H2").Resize(n - 1, 3) = Application.WorksheetFunction.Transpose(brr)
    
47.        [H1].Value = "白天工作小时数"
    
48.        [I1].Value = "晚上加班小时数"
    
49.        [J1].Value = "至二十点次数"
    
50. 
    
51. 
    
52. 
    
53.         '以下代码是根据E列F列计算出来的H列I列数据，生成一张每人一个月的总时间表格，放在N列开始
    
54.         n = .Cells(.Rows.Count, 1).End(xlUp).Row
    
55.         arr = .Range("a2:J" & n)
    
56.         Set dA = CreateObject("Scripting.Dictionary")
    
57.         Set dB = CreateObject("Scripting.Dictionary")
    
58.         Set dC = CreateObject("Scripting.Dictionary")
    
59.         Set dD = CreateObject("Scripting.Dictionary")
    
60.         Set dE = CreateObject("Scripting.Dictionary")
    
61.         Set dF = CreateObject("Scripting.Dictionary")
    
62.         Set dG = CreateObject("Scripting.Dictionary")
    
63.         Set dH = CreateObject("Scripting.Dictionary")
    
64.         Set dI = CreateObject("Scripting.Dictionary")
    
65.         Set dJ = CreateObject("Scripting.Dictionary")
    
66.         For i = 1 To n - 1
    
67.             If Weekday(arr(i, 2), 2) < 6 Then
    
68.                 If dA.Exists(arr(i, 1)) Then
    
69.                     dA(arr(i, 1)) = dA(arr(i, 1)) + arr(i, 8)
    
70.                     dB(arr(i, 1)) = dB(arr(i, 1)) + arr(i, 9)
    
71.                     dC(arr(i, 1)) = dC(arr(i, 1)) + IIf(arr(i, 10) = "", 0, arr(i, 10))
    
72.                 Else
    
73.                     dA.Add arr(i, 1), arr(i, 8)
    
74.                     dB.Add arr(i, 1), arr(i, 9)
    
75.                     dC.Add arr(i, 1), IIf(arr(i, 10) = "", 0, arr(i, 10))
    
76.                 End If
    
77.             ElseIf Weekday(arr(i, 2), 2) = 6 Then
    
78.                 If dA.Exists(arr(i, 1)) Then
    
79.                     dD(arr(i, 1)) = dD(arr(i, 1)) + arr(i, 8)
    
80.                     dE(arr(i, 1)) = dE(arr(i, 1)) + arr(i, 9)
    
81.                     dF(arr(i, 1)) = dF(arr(i, 1)) + IIf(arr(i, 10) = "", 0, arr(i, 10))
    
82.                 Else
    
83.                     dD.Add arr(i, 1), arr(i, 8)
    
84.                     dE.Add arr(i, 1), arr(i, 9)
    
85.                     dF.Add arr(i, 1), IIf(arr(i, 10) = "", 0, arr(i, 10))
    
86.                 End If
    
87.             ElseIf Weekday(arr(i, 2), 2) = 7 Then
    
88.                 If dA.Exists(arr(i, 1)) Then
    
89.                     dG(arr(i, 1)) = dG(arr(i, 1)) + arr(i, 8)
    
90.                     dH(arr(i, 1)) = dH(arr(i, 1)) + arr(i, 9)
    
91.                     dI(arr(i, 1)) = dI(arr(i, 1)) + IIf(arr(i, 10) = "", 0, arr(i, 10))
    
92.                 Else
    
93.                     dG.Add arr(i, 1), arr(i, 8)
    
94.                     dH.Add arr(i, 1), arr(i, 9)
    
95.                     dI.Add arr(i, 1), IIf(arr(i, 10) = "", 0, arr(i, 10))
    
96.                 End If
    
97. 
    
98.             End If
    
99.                     dJ.Add arr(i, 1), arr(i, 7)
    
100.         Next
     
101.         x = 1
     
102.         For Each k In dA.keys
     
103.             x = x + 1
     
104.             .Cells(x, 14) = k
     
105.             .Cells(x, 15) = dA(k)
     
106.             .Cells(x, 16) = dB(k)
     
107.             .Cells(x, 17) = dC(k)
     
108.             .Cells(x, 18) = dD(k)
     
109.             .Cells(x, 19) = dE(k)
     
110.             .Cells(x, 20) = dF(k)
     
111.             .Cells(x, 21) = dG(k)
     
112.             .Cells(x, 22) = dH(k)
     
113.             .Cells(x, 23) = dI(k)
     
114.             .Cells(x, 24) = dJ(k)
     
115.         Next
     
116.      [N1].Value = "姓名"
     
117.      [O1].Value = "正常出勤时间"
     
118.      [P1].Value = "加点时间"
     
119.      [Q1].Value = "加点次数"
     
120.      [R1].Value = "周六白天"
     
121.      [S1].Value = "周六晚上"
     
122.      [T1].Value = "周六加点次数"
     
123.      [U1].Value = "周日白天"
     
124.      [V1].Value = "周日晚上"
     
125.      [W1].Value = "周日加点次数"
     
126.      [X1].Value = "部门"
     
127. 
     
128.      '以下代码是为E列F列有空白格的行涂上黄色
     
129.      [a:j].Interior.ColorIndex = xlNone
     
130.      For i = 2 To [e:f].Find("*", searchdirection:=xlPrevious).Row
     
131.        If Cells(i, 5) <> "" And Cells(i, 6) = "" Or Cells(i, 6) <> "" And Cells(i, 5) = "" Then
     
132.            Cells(i, 1).Resize(1, 10).Interior.ColorIndex = 6
     
133.        End If
     
134.      Next
     
135.     End With
     
136.         
     
137. End Sub
     
138. Function 取值(ByVal x As Double) As Double
     
139.     If x - Int(x) > 0.83333 Then
     
140.         取值 = Round(x, 0)
     
141.     ElseIf x - Int(x) < 0.33333 Then
     
142.         取值 = Int(x)
     
143.     Else
     
144.         取值 = Int(x) + 0.5
     
145.     End If
     
146. End Function
     

复制代码

按“雪舞子”的方法帮你修改了一下，运行后完全没有错误了：