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
Can u please help me in writing a macro in excel as below:

1) When we click on the command button, it should prompt for entering the number in the input box.

2) After entering the number, it should take us to the cell that has the numer.

3) and coresponding cell should be filled with current time.

Template is as below:

When i click the command button and enter the number 307304 in the input box. it should take me to the particular cell and that start time should be captured. if i click again end time should be captured. (these times should be current time).

Employee # Start Time End Time
307301
307302
307303
307304
307305
307306
307307
307308
307309
307310

2 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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.


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   



0
Sathish Kumar.B
Jul 11, 2011 at 01:41 AM
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.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 11, 2011 at 12:54 PM
#1 : replace
= Now
with
= Date

for #2
for that you have to use protection.
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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
0
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.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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.
0