Update value on another new line of row if another entry has been made for same ID number

[hsolanki]

Hi i have a code whereby it works fine for when it finds the Staff ID for 1st Job allocated then it will update timestamp on the same line in column "M" when list has been selected from list box however when i try to update the another new line of row for the same person with different Job number it will still again update first Job line of row and not the 2nd Job line of row when i tried to add the 3rd Job.

me.lstselector.list(x,3) - it is the Staff ID in column G
me.lstselector.list(x,2) - it is the Job number in column F

i need code to find using Job number and staff ID, when matches it both then to updates time on same row every time in column M

here its the code

dim findvalue

Sheet1.Unprotect codePassword:="1234"
lCol = Me.lstSelector.List(x, 3)
Set findvalue = Sheet1.Range("G:G").Find(What:=lCol, LookIn:=xlValues).Offset(0, -2)
findvalue.Offset(0, 8).Value = Format(Now, "HH:MM:SS")

 

[JLGWhiz]

Maybe this:

Dim adr As String
Sheet1.Unprotect codePassword:="1234"
lCol = Me.lstSelector.List(x, 3)
Set findvalue = Sheet1.Range("G:G").Find(What:=lCol, LookIn:=xlValues)
    If Not findvalue Is Nothing Then
        adr = findvalue.Address
        Do
            If findvalue.Offset(, -1).Value = Me.lstlelector(x, 2) Then
                findvalue.Offset(0, 6).Value = Format(Now, "HH:MM:SS")
                Exit Do
            End If
            Set findvalue = Sheet1.Range("G:G").FindNext(findvalue)
        Loop While findvalue.Address <> adr
        Set findvalue = Nothing
    End If

Note that I change the findvalue range to remain in column G. If you were using it elsewhere in the code for another purpose you will need to add the offset(, -2) to each place it is used. But if you change it back in this snippet, then the FindNext function will not work.

 

[hsolanki]

Hi thank you for your reply and response however i get an error mistypes on below line

If findvalue.Offset(, -1).Value = Me.lstselector(x, 2) Then

 

[JLGWhiz]

My error in typing instead of copying.
Should be:

If findvalue.Offset(, -1).Value = Me.lstselector.List(x, 2) Then

 

[hsolanki]

Hi Thank you very much for all your help. it worked as i excepted .

 

[JLGWhiz]

Hi Thank you very much for all your help. it worked as i excepted .

You're welcome,
regards, JLG