首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 开发语言 > VB >

分享一段统计工作时间的VB代码,该怎么处理

2012-01-13 
分享一段统计工作时间的VB代码Private Sub Worksheet_SelectionChange(ByVal Target As Range)Cells(3, V

分享一段统计工作时间的VB代码
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  Cells(3, "V") = "工作时长"
  Cells(3, "X") = "姓名"
  Cells(3, "Y") = "月度总工时"
  Cells(3, "Z") = "标准工时"
  Cells(3, "AA") = "日平均工时"
  Range("V3").HorizontalAlignment = Excel.xlCenter
  Range("V:V").ColumnWidth = 12
  Range("Y:Y").ColumnWidth = 15
  Range("Z:Z").ColumnWidth = 10
  Range("AA:AA").ColumnWidth = 15
  Range("Y3").HorizontalAlignment = Excel.xlCenter
   
  namerow = 4
  nametext = ""
  Dim minutes
  Dim summinutes
  summinutes = 0
   
  Dim days
  days = 15

  Dim begintime
  Dim endtime
  For m = 4 To 200
  endtime = Cells(m, "F")
  If endtime = "" Then '行末尾
  '最后一个人统计
  If m > 4 Then
  'If summinutes >= 40 * 60 Then
  Cells(namerow - 1, "Y") = (summinutes) \ 60 & "小时" & (summinutes) Mod 60 & "分钟"
  Cells(namerow - 1, "Y").Interior.ColorIndex = 0
  Cells(namerow - 1, "Z") = (8 * 60 * days) \ 60 & "小时" ' & (8 * 60 * days) Mod 60 & "分钟"
  Cells(namerow - 1, "AA") = (summinutes \ days) \ 60 & "小时" & (summinutes \ days) Mod 60 & "分钟"
  ' Else
  'Cells(namerow - 1, "Y") = (40 * 60 - summinutes) \ 60 & "小时" & (40 * 60 - summinutes) Mod 60 & "分钟"
  If (summinutes - days * 8 * 60) < 0 Then
  Cells(namerow - 1, "Y").Interior.ColorIndex = 3
  End If
  End If
  '边框
  'Range("X3","Y" & namerow)
  Exit For
  End If

  If nametext <> Cells(m, "D") Then
  nametext = Cells(m, "D")
  Cells(namerow, "X") = nametext
   
  '计算总计
  If m > 4 Then
  'If summinutes >= 40 * 60 Then
  Cells(namerow - 1, "Y") = (summinutes) \ 60 & "小时" & (summinutes) Mod 60 & "分钟"
  Cells(namerow - 1, "Y").Interior.ColorIndex = 0
  Cells(namerow - 1, "Z") = (8 * 60 * days) \ 60 & "小时" ' & (8 * 60 * days) Mod 60 & "分钟"
  Cells(namerow - 1, "AA") = (summinutes \ days) \ 60 & "小时" & (summinutes \ days) Mod 60 & "分钟"
  'Else
  ' Cells(namerow - 1, "Y") = "缺" & (40 * 60 - summinutes) \ 60 & "小时" & (40 * 60 - summinutes) Mod 60 & "分钟"
  If (summinutes - days * 8 * 60) < 0 Then
  Cells(namerow - 1, "Y").Interior.ColorIndex = 3
  End If
  End If
  summinutes = 0
  namerow = namerow + 1
  End If
   
  begintime = endtime
  For n = 7 To 21
  If Cells(m, n) > endtime Then


  endtime = Cells(m, n)
  Else
  Exit For '空单元格
  End If
  Next n

  If Len(begintime) = 5 Then
  minutes = Left(endtime, 2) * 60 + Right(endtime, 2) - (Left(begintime, 2) * 60 + Right(begintime, 2)) - 90 '工作总分钟
  Else
  minutes = 0
  End If
  If minutes > 0 Then
  Cells(m, "V") = TimeSerial(0, minutes, 0)
  summinutes = summinutes + minutes '总分钟
  Else
  Cells(m, "V") = ""
  End If
  Next m

End Sub




[解决办法]
感谢分享
[解决办法]
嗯,8错
[解决办法]
懂得分享,就懂得了编程的乐趣。
[解决办法]
楼上3头大牛都感谢了……
[解决办法]
感谢分享
[解决办法]
收藏了
[解决办法]
8错8错

热点排行