Userform to search and update rows with multiple matches

[mhaidar81]

Hello everyone,

I have a userform with a search box that looks for a match in Sheet1 Column A. If a match is found, certain cells from the matched row are displayed in the userform in textboxes. I am able to modify these textboxes and write the changes back to the cells they came from.

The issue that I am running into is when I have multiple matches. When I click the Search button again, I am able to see the cells from the second match displayed in the textboxes. However, when I modify them, the changes are applied to the first match. How can I modify the textboxes and apply the changes to the correct match? This is my code. Also, is there a way to shorten this code? I really appreciate any help

Private Sub cmdUpdate_Click()

    Dim wsTimesheet, wsMasterPay, wsEmployeeInformation As Worksheet
    
    Set wsTimesheet = Worksheets("Timesheet")
    Set wsMasterPay = Worksheets("Master Pay")
    Set wsEmployeeInformation = Worksheets("Employee Information")
    
    Dim lastRow, i As Long
    Dim searchText As String
    Dim results15(1 To 16), resultsEnd(1 To 12), resultsCalc(1 To 7) As Variant
    
    lastRow = wsTimesheet.Cells(wsTimesheet.Rows.Count, "A").End(xlUp).Row ' Get the last row of column A
    
    searchText = Me.txtSearch.Value
      
    For i = 2 To lastRow ' Loop through each row in column A, starting from row 2
    
    If wsTimesheet.Cells(i, 1).Value = searchText Then ' Check if the value in column A matches the search text
    'Get updated values from textboxes
    
            results15(1) = Me.Pending.Value
            results15(2) = Me.Adjustment.Value
            results15(3) = Me.AdjustmentDays.Value
            results15(4) = Me.NewOldSalary.Value
            results15(5) = Me.Reg1.Value
            results15(6) = Me.OT1.Value
            results15(7) = Me.WHOT1.Value
            results15(8) = Me.WH1.Value
            results15(9) = Me.PO1.Value
            results15(10) = Me.RR1.Value
            results15(11) = Me.SL1.Value
            results15(12) = Me.NWH1.Value
            results15(13) = Me.BER1.Value
            results15(14) = Me.WED1.Value
            results15(15) = Me.AWOL1.Value
            results15(16) = Me.LWOP1.Value
            resultsEnd(1) = Me.Reg2.Value
            resultsEnd(2) = Me.OT2.Value
            resultsEnd(3) = Me.WHOT2.Value
            resultsEnd(4) = Me.WH2.Value
            resultsEnd(5) = Me.PO2.Value
            resultsEnd(6) = Me.RR2.Value
            resultsEnd(7) = Me.SL2.Value
            resultsEnd(8) = Me.NWH2.Value
            resultsEnd(9) = Me.BER2.Value
            resultsEnd(10) = Me.WED2.Value
            resultsEnd(11) = Me.AWOL2.Value
            resultsEnd(12) = Me.LWOP2.Value
            resultsCalc(1) = Me.Gross.Value
            resultsCalc(2) = Me.SS.Value
            resultsCalc(3) = Me.PIT.Value
            resultsCalc(4) = Me.PCV.Value
            resultsCalc(5) = Me.AddDeduct.Value
            resultsCalc(6) = Me.COLA.Value
            resultsCalc(7) = Me.NetIncome.Value
            updateComments = Me.Comments.Value
                      
            ' Update the corresponding cells in Excel using an array
            wsTimesheet.Cells(i, 3).Resize(1, 16).Value = results15
            wsTimesheet.Cells(i, 20).Resize(1, 12).Value = resultsEnd
            wsTimesheet.Cells(i, 46).Resize(1, 7).Value = resultsCalc
            wsTimesheet.Cells(i, 35).Value = updateComments
    
            ' Display a message box to confirm that the update was successful
            MsgBox "Update successful.", vbOKOnly + vbInformation, "Update Results"
            Exit Sub ' Exit the loop after the first match is found
        End If
    Next i
        
End Sub

 

[dmt32]

It works like a charm! Thank you again for all your help.

most welcome - appreciate your feedback

Dave