Listbox vs Textbox

[rickblunt]

Hello,
I am using the code below to pass information on a userform to a WS. It is a code that I have used for years and has been very reliable. I am writing up a new userform WB for a new project and for some reason I cannot get this code to work correctly. Well, more exactly it works fine for the "Device List" WS - everything there is good, but when it moves to the "Repair Log" WS section it does not place the values in the correct row. Instead of placing the data on the row that corresponds with DeviceId.Value, it always puts it on the next blank row at the end, regardless of where that DeviceID.Value is.

The only difference that I can see in my workbooks is that I usually use a textbox for these entries on my userform, but in this case I need to use a listbox. Could this be the reason why its not working correctly? I appreciate your input - thanks,

Private Sub CommentButton_Click()

    Dim i As Long
    Dim Lastrow As Long
    Dim Found As Range
       
   
    Worksheets("Device List").Activate
    
    Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    
        For i = 1 To Lastrow
            If Cells(i, 8).Value = "FAIL" And Cells(i, 7).Value = "" Then
            Cells(i, 7).Value = RepairComments.Value
            
    
    'insert code to pass the comments to the relevant repair log row
    
    Sheets("Repair Log").Activate
    
        Set Found = Sheets("Repair Log").Range("A:A").Find(What:=Me.DeviceId.Value, _
                                                       LookIn:=xlValues, _
                                                       LookAt:=xlWhole, _
                                                       SearchOrder:=xlByRows, _
                                                       SearchDirection:=xlNext, _
                                                       MatchCase:=False)
        If Found Is Nothing Then
            MsgBox "No match for " & Me.DeviceId.Value, , "No Match Found"
        Else
            Found.Offset(0, 2).Value = Me.RepairComments.Value
            
     End If
    
   
    Exit For
    End If
    Next
    
'some more code here..... :)
    
End Sub

 

[Akuini]

Untested, but try removing this line:

Sheets("Repair Log").Activate

 

[Akuini]

Sorry, you have "Exit For" before the "End If", so I don't think removing the line would matter.

I usually use a textbox for these entries on my userform, but in this case I need to use a listbox.

Which one is the listbox? DeviceId?
For debugging, try checking the value first with a message box. See if the value is correct.

        Sheets("Repair Log").Activate
        MsgBox Me.DeviceId.Value

 

[rickblunt]

Sorry, you have "Exit For" before the "End If", so I don't think removing the line would matter.

Which one is the listbox? DeviceId?
For debugging, try checking the value first with a message box. See if the value is correct.

        Sheets("Repair Log").Activate
        MsgBox Me.DeviceId.Value

yeah, the listbox is holding the value correctly, its just not putting it in the correct spot on the WS. For some reason the "Set Found" portion is not finding the correct row, not matching the deviceid value to the matching one on the sheet - I cannot figure why because the syntax of the search appears to be correct to me. I thought maybe a listbox needed different wording or something in code.....