VBA 创建打卡表

VBA 创建打卡表

我想创建一个记录我制作的时钟的记录器。上班时,我点击一个按钮,它会在右侧单元格中添加小时数,这样它就可以计算我的时间了。但我想使它自动化。我的问题是 VBA... 有人能帮我吗?这就是我需要的。

通过选择界面选项卡上的按钮,它会将实际时间值发送到另一个选项卡。它首先查看哪一行是今天的行以及哪一列可以粘贴值(尽可能靠近左侧)我想添加几个时钟,而不仅仅是一个。

在第二个选项卡中,我使用 C 列查找今天的日期并放置一个“x”。也是为了美观。我试图一步一步地完成,但我不知道可以使用的公式和属性...

Sub Clock()

Dim foundDate As Range
Set foundDate = Range("C:C").Find("x")

End Sub

请参阅下面的图片。

感谢您的帮助。

日历,电池 L211

界面按钮


以下是受您的评论启发的代码(我尝试根据我的需要进行理解和修改):

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格式是可编辑的。

根据需要调整公式中的单元格引用。

相关内容