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
- Mobile number list with name - Guide
- How to change your best friends list on snapchat to 3 - Guide
- Counter strike 1.6 cheats list - Guide
- Whatsapp country code list - Guide
- Amd crossfire compatibility list - Guide
2 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
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
Moderator
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.