Scan in time and location to timesheet in Excel. Track time spent on different jobs from timesheet. Free templates and templates with code are available for purchase for $50 USD
www.easyexcela...
Addition Templates may be available on request
Click this link to check out my one-on-one training www.calendly.co...
For more help visit my website www.easyexcelanswers.com or email me at easyexcelanswers@gmail.com.
Contact me regarding customizing this template for your needs.
Click for online Excel Consulting www.calendly.co...
I am able to provide online help on your computer at a reasonable rate.
Check out Crowdcast for creating your webinars
app.linkmink.c...
I use Tube Buddy to help promote my videos
Check them out
www.Tubebuddy....
Follow me on Facebook
/ easyexcel.answers
IG @barbhendersonconsulting
You can help and generate a translation to you own language
www.youtube.com...
*this description may contain affiliate links. When you click them, I may receive a small commission at no extra cost to you. I only recommend products and services that I've used or have experience with.
How to insert VBA code in Excel • How to insert VBA code...
Sub enterloc()
Dim r As Long
Dim empcode As String
Dim rng, day As Range
Dim location As String
empcode = Sheet1.Cells(2, 1)
If empcode = "" Then Exit Sub
If empcode(not equal to) "" Then
Sheet2.Activate
Sheet2.Cells(2, 1).Value = empcode
Sheet2.Cells(2, 2).Select
'location = Sheet2.Cells(2, 2).Value
End If
End Sub
Sub tracktime()
Dim r As Long
Dim rng, day As Range
Dim lrng As Range
Dim grandt As Double
Dim rownumber, col As Long
Dim Total, wktot As Double
Dim gtot As Double
Dim Timein As Date
Dim Timeout As Date
empcode = Sheet1.Cells(2, 1)
location = Sheet2.Cells(2, 2).Value
If empcode = "" Then Exit Sub
Sheet1.Activate
With Sheet1
col = 4
'search for today
For col = 4 To 16
If .Cells(1, col).Value = Date Then GoTo findname
Next col
findname:
If empcode not equl to "" Then
Set rng = ActiveSheet.Columns("c:c").Find(what:=empcode, _
LookIn:=xlValues, lookat:=xlWhole)
rownumber = rng.Row
' find location
Set lrng = ActiveSheet.Range(Cells(rownumber, 2), Cells(rownumber + 4, 2)).Find(what:=location, _
LookIn:=xlValues, lookat:=xlWhole)
If lrng Is Nothing Then
Do While rownumber less than (rownumber + 5)
Sheet1.Cells(rownumber, col).Select
If ActiveCell.Value = "" And Sheet1.Cells(rownumber, 2).Value = "" Then
ActiveCell.Value = Time
ActiveCell.NumberFormat = "hh:mm"
Sheet1.Cells(rownumber, 2).Value = location
GoTo ende
End If
rownumber = rownumber + 1
Loop
Else
rownumber = lrng.Row
Sheet1.Cells(rownumber, col).Select
If ActiveCell.Value = "" Then
ActiveCell.Value = Time
ActiveCell.NumberFormat = "hh:mm"
GoTo ende
Else
'ActiveCell.Offset(0, 1).Select
GoTo outtime
End If
End If
outtime:
If ActiveCell not equal to "" And Sheet1.Cells(rownumber, 2).Value = location Then
ActiveCell.Offset(0, 1).Select
If ActiveCell.Value = "" Then
ActiveCell.Value = Time
ActiveCell.NumberFormat = "hh:mm"
Timein = CDate(Cells(rownumber, col).Value)
Timeout = CDate(Cells(rownumber, col).Offset(0, 1).Value)
Total = TimeValue(Timeout) - TimeValue(Timein)
Debug.Print Total
Debug.Print Format(Total, "[h]:mm")
wktot = Cells(rownumber, 18).Value
Cells(rownumber, 18).NumberFormat = "[h]:mm"
Cells(rownumber, 18).Value = Cells(rownumber, 18).Value + Total
'Cells(r, 18).NumberFormat = "[h]:mm"
Cells(rownumber, 18).Value = Cells(rownumber, 18).Value + Total
End If
GoTo ende
End If
End If
'End If
ende:
ActiveSheet.Cells(2, 1).Value = ""
End With
Sheet2.Cells(2, 2).ClearContents
Sheet2.Cells(2, 1).ClearContents
ActiveSheet.Cells(2, 1).Select
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("A2")) Is Nothing Then
Call enterloc
End If
End Sub
Негізгі бет Scan in time and location to timesheet in Excel
Пікірлер: 2