我想创建一个记录我制作的时钟的记录器。上班时,我点击一个按钮,它会在右侧单元格中添加小时数,这样它就可以计算我的时间了。但我想使它自动化。我的问题是 VBA... 有人能帮我吗?这就是我需要的。
通过选择界面选项卡上的按钮,它会将实际时间值发送到另一个选项卡。它首先查看哪一行是今天的行以及哪一列可以粘贴值(尽可能靠近左侧)我想添加几个时钟,而不仅仅是一个。
在第二个选项卡中,我使用 C 列查找今天的日期并放置一个“x”。也是为了美观。我试图一步一步地完成,但我不知道可以使用的公式和属性...
Sub Clock()
Dim foundDate As Range
Set foundDate = Range("C:C").Find("x")
End Sub
请参阅下面的图片。
感谢您的帮助。
以下是受您的评论启发的代码(我尝试根据我的需要进行理解和修改):
Sub mark_today()
Dim ws As Worksheet
Set ws = Worksheets("Calendrier")
Dim todayCel As Range
On Error Resume Next ' if there's nothing found
Set todayCel = ws.Range("E:E").Find(what:=Date)
On Error GoTo 0
If todayCel Is Nothing Then
' Do something if today's date can't be found
ElseIf IsEmpty(ws.Cells(todayCel.Row, "I")) = True Then
ws.Cells(todayCel.Row, "I").Value = Now
ws.Cells(todayCel.Row, "I").NumberFormat = "HH:mm:ss"
ElseIf IsEmpty(ws.Cells(todayCel.Row, "J")) = True Then
ws.Cells(todayCel.Row, "J").Value = Now
ws.Cells(todayCel.Row, "J").NumberFormat = "HH:mm:ss"
ElseIf IsEmpty(ws.Cells(todayCel.Row, "L")) = True Then
ws.Cells(todayCel.Row, "L").Value = Now
ws.Cells(todayCel.Row, "L").NumberFormat = "HH:mm:ss"
ElseIf IsEmpty(ws.Cells(todayCel.Row, "M")) = True Then
ws.Cells(todayCel.Row, "M").Value = Now
ws.Cells(todayCel.Row, "M").NumberFormat = "HH:mm:ss"
ElseIf IsEmpty(ws.Cells(todayCel.Row, "O")) = True Then
ws.Cells(todayCel.Row, "O").Value = Now
ws.Cells(todayCel.Row, "O").NumberFormat = "HH:mm:ss"
ElseIf IsEmpty(ws.Cells(todayCel.Row, "P")) = True Then
ws.Cells(todayCel.Row, "P").Value = Now
ws.Cells(todayCel.Row, "P").NumberFormat = "HH:mm:ss"
ElseIf IsEmpty(ws.Cells(todayCel.Row, "R")) = True Then
ws.Cells(todayCel.Row, "R").Value = Now
ws.Cells(todayCel.Row, "R").NumberFormat = "HH:mm:ss"
ElseIf IsEmpty(ws.Cells(todayCel.Row, "S")) = True Then
ws.Cells(todayCel.Row, "S").Value = Now
ws.Cells(todayCel.Row, "S").NumberFormat = "HH:mm:ss"
ElseIf IsEmpty(ws.Cells(todayCel.Row, "U")) = True Then
ws.Cells(todayCel.Row, "U").Value = Now
ws.Cells(todayCel.Row, "U").NumberFormat = "HH:mm:ss"
ElseIf IsEmpty(ws.Cells(todayCel.Row, "V")) = True Then
ws.Cells(todayCel.Row, "V").Value = Now
ws.Cells(todayCel.Row, "V").NumberFormat = "HH:mm:ss"
ElseIf IsEmpty(ws.Cells(todayCel.Row, "X")) = True Then
ws.Cells(todayCel.Row, "X").Value = Now
ws.Cells(todayCel.Row, "X").NumberFormat = "HH:mm:ss"
ElseIf IsEmpty(ws.Cells(todayCel.Row, "Y")) = True Then
ws.Cells(todayCel.Row, "Y").Value = Now
ws.Cells(todayCel.Row, "Y").NumberFormat = "HH:mm:ss"
ElseIf IsEmpty(ws.Cells(todayCel.Row, "AA")) = True Then
ws.Cells(todayCel.Row, "AA").Value = Now
ws.Cells(todayCel.Row, "AA").NumberFormat = "HH:mm:ss"
ElseIf IsEmpty(ws.Cells(todayCel.Row, "AB")) = True Then
ws.Cells(todayCel.Row, "AB").Value = Now
ws.Cells(todayCel.Row, "AB").NumberFormat = "HH:mm:ss"
ElseIf IsEmpty(ws.Cells(todayCel.Row, "AD")) = True Then
ws.Cells(todayCel.Row, "AD").Value = Now
ws.Cells(todayCel.Row, "AD").NumberFormat = "HH:mm:ss"
ElseIf IsEmpty(ws.Cells(todayCel.Row, "AE")) = True Then
ws.Cells(todayCel.Row, "AE").Value = Now
ws.Cells(todayCel.Row, "AE").NumberFormat = "HH:mm:ss"
End If
End Sub
答案1
该子程序将在“日历”工作表的 E 列中查找今天的日期,如果找到,则在该行的 L 列中放置一个“X”。
Sub mark_today()
Dim ws As Worksheet
Set ws = Worksheets("Calendrier")
Dim todayCel As Range
On Error Resume Next ' if there's nothing found
Set todayCel = ws.Range("E:E").Find(what:=Date)
On Error GoTo 0
If todayCel Is Nothing Then
' Do something if today's date can't be found
Else
Cells(todayCel.Row, "L").Value = "X"
End If
End Sub
只需将该宏分配给您的按钮即可。
答案2
这种方法将帮助你自动化,时钟/考勤表:
怎么运行的:
- 单元格中的公式
L2
:
=IF(ISBLANK($M2),"","X")
在单元格中输入此公式
O2
:=IF(OR(ISBLANK($M2),ISBLANK($N2)),"",INT($N2-$M2)&" days "&TEXT($N2-$M2,"h"" hrs ""m"" mins """))
填写两个公式。
复制&粘贴此 VBA 代码作为标准模块。
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Not Intersect(Target, Range("M2:N5")) Is Nothing Then Cancel = True Target.Formula = Format(Now(), "mm/dd/yyyy HH:mm:ss AM/PM") End If End Sub
注意:
mm/dd/yy hh:mm:ss AM/PM
在范围内应用格式M2:N5
。双击
M2:N5
在您/员工姓名旁边的单元格中输入及时也时差。- 在上面的 VBA 代码中,范围
Range("M2:N5")
和Date Time
格式是可编辑的。
根据需要调整公式中的单元格引用。