How to find a number from the list
Solved/Closed
sathish
-
Jun 30, 2011 at 02:47 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 19, 2011 at 09:58 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 19, 2011 at 09:58 AM
Related:
- How to find a number from the list
- How to change your best friends list on snapchat to 3 - Guide
- Counter strike 1.6 cheats list - Guide
- Amd crossfire gpu list - Guide
- Epic games free games list - Guide
- Lava mobile reset codes list ✓ - Phones, PDA & GPS Forum
2 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Contributor
Last seen
December 27, 2022
555
Jul 1, 2011 at 10:09 AM
Jul 1, 2011 at 10:09 AM
Hi Sathish,
What a nice query, if someone else doesn't I will look into it next week.
Best regards,
Trowa
What a nice query, if someone else doesn't I will look into it next week.
Best regards,
Trowa
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jul 2, 2011 at 08:56 AM
Jul 2, 2011 at 08:56 AM
Try this. The routine to use is doTimeStamp
The idea is that you would attach this routine to the command button. When yo click on it, it will ask for emp id and will fill in the start date (if it is blank) or end date (if it is blank) and then prompt you again for the next id. It will continue to ask you for id till you enter a blank and that point it will quit.
The idea is that you would attach this routine to the command button. When yo click on it, it will ask for emp id and will fill in the start date (if it is blank) or end date (if it is blank) and then prompt you again for the next id. It will continue to ask you for id till you enter a blank and that point it will quit.
Option Explicit
Public Sub doTimeStamp()
Dim lRow As Long
Dim sSearchText As String
Dim lEmpID As Long
Dim sTgtSheet As String
'name of the sheet where the ids are
sTgtSheet = "Sheet1"
Do
sSearchText = InputBox("Please Enter the Employee ID", "Time Recording")
sSearchText = Trim(sSearchText)
If (sSearchText = vbNullString) _
Then
'no data was entered. then quit
GoTo Loop_Bottom
End If
If Not (IsNumeric(sSearchText)) _
Then
'text entered was not numeric.
MsgBox "Invalid Employee ID. Employee ID can be only digits. Try Again", vbExclamation + vbOKOnly
GoTo Loop_Bottom
End If
If (InStr(1, sSearchText, ".") > 0) _
Then
'text entered had a decimal.
MsgBox "Invalid Employee ID. Employee ID can be only digits. Try Again", vbExclamation + vbOKOnly
GoTo Loop_Bottom
End If
'locate the row in column 1
lRow = getItemLocation(sSearchText, Sheets(sTgtSheet).Columns(1))
If (lRow = 0) _
Then
'search returned no hit
MsgBox "Employee ID Not Found. Try Again", vbInformation + vbOKOnly
GoTo Loop_Bottom
End If
If (Sheets(sTgtSheet).Cells(lRow, "B") = vbNullString) _
Then
'cell of the found row has column B empty
Sheets(sTgtSheet).Cells(lRow, "B") = Now
ElseIf (Sheets(sTgtSheet).Cells(lRow, "C") = vbNullString) _
Then
'cell of the found row has column C empty
Sheets(sTgtSheet).Cells(lRow, "C") = Now
Else
'cell of the found row has column B and C filled in
MsgBox "Start and End Time has been already recorded for Employee " & sSearchText , vbInformation + vbOKOnly
End If
Loop_Bottom:
' loop till sSearchText is a blank
Loop While (sSearchText <> vbNullString)
End Sub
Public Function getItemLocation(sLookFor As String, _
rngSearch As Range, _
Optional bFullString As Boolean = True, _
Optional bLastOccurance As Boolean = True, _
Optional bFindRow As Boolean = True) As Long
'find the first/last row/column within a range for a specific string
Dim Cell As Range
Dim iLookAt As Integer
Dim iSearchDir As Integer
Dim iSearchOdr As Integer
If (bFullString) _
Then
iLookAt = xlWhole
Else
iLookAt = xlPart
End If
If (bLastOccurance) _
Then
iSearchDir = xlPrevious
Else
iSearchDir = xlNext
End If
If Not (bFindRow) _
Then
iSearchOdr = xlByColumns
Else
iSearchOdr = xlByRows
End If
With rngSearch
If (bLastOccurance) _
Then
Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir)
Else
Set Cell = .Find(sLookFor, .Cells(.Rows.Count, .Columns.Count), xlValues, iLookAt, iSearchOdr, iSearchDir)
End If
End With
If Cell Is Nothing Then
getItemLocation = 0
ElseIf Not (bFindRow) _
Then
getItemLocation = Cell.Column
Else
getItemLocation = Cell.Row
End If
Set Cell = Nothing
End Function
Thank you very much for the help. I tried the macros & it's working great. But I needs only small change in this. Can you please assist me.
1) The cell captures the date and time. But I need the cell to capture only the current time.
2) When the time is entered in the cell, it should automatically save the spreadsheet and it should not allow the employee to make changes again. The changes should be made only if he/she knows the password..
Please help me on this. It would be very helpful.
1) The cell captures the date and time. But I need the cell to capture only the current time.
2) When the time is entered in the cell, it should automatically save the spreadsheet and it should not allow the employee to make changes again. The changes should be made only if he/she knows the password..
Please help me on this. It would be very helpful.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jul 11, 2011 at 12:54 PM
Jul 11, 2011 at 12:54 PM
#1 : replace
= Now
with
= Date
for #2
for that you have to use protection.
= Now
with
= Date
for #2
for that you have to use protection.
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Contributor
Last seen
December 27, 2022
555
Jul 12, 2011 at 09:12 AM
Jul 12, 2011 at 09:12 AM
I think Riz was a little hasty in his reply.
1. To only diplay time replace
= Now
with
= Now - Date
Make sure you format your cell as Time.
2. To save your workbook automatically add the following code line:
ActiveWorkbook.Save
Best regards,
Trowa
1. To only diplay time replace
= Now
with
= Now - Date
Make sure you format your cell as Time.
2. To save your workbook automatically add the following code line:
ActiveWorkbook.Save
Best regards,
Trowa
Thank you very much for the response. It is very helpful & I have learnt something by you.
But, the macros is run only once, In the above macro, start time is captured in cell "B" & End time in cell "C". Same as it should continue in other cells too as, start time in cell "D" & End time in cell "E" & so on.
How to do it.
But, the macros is run only once, In the above macro, start time is captured in cell "B" & End time in cell "C". Same as it should continue in other cells too as, start time in cell "D" & End time in cell "E" & so on.
How to do it.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jul 15, 2011 at 05:50 AM
Jul 15, 2011 at 05:50 AM
and question is why not B and why D and why D and not J. You need to give more detailed information about your business case.