Code to locate data and then update other cells in that row from User Form

[K1600]

I have a user form which pre-loads with data from a 2nd spreadsheet. The purpose of the user form is to be able to change/update that information and then put the information back to the 2nd spreadsheet. The column that will never change is a PIN number which is in column 'A' but data in the other columns (B-M) can be updated from the userform. I have my userform working up to the point where it takes the data from the 2nd spreadsheet and puts it in the appropriate text box (or combo box) but I am struggling to work out how to get the data to go back to the 2nd spreadsheet when 'update' is pressed. I in essence need it to find the appropriate PIN number in column 'A' and then paste the relevant bits to the adjacent columns on the same row accordingly. I currently have the following (sanitised to remove company info) which does not error but also does not update the spreadsheet:

Private Sub CmdSubmit_Click() Dim wbk As Workbook Dim i As Long Dim rng As Range Set wbk = Workbooks.Open("\\XXXX\Returns v.2.0.xlsx", WriteResPassword:="XXXX", IgnoreReadOnlyRecommended:=True) Set rng = Range("A1") With wbk.Sheets("Admin Users") For i = 1 To Application.CountIf(Range("A:A"), TxtPIN.value) rng.Offset(0, 7).value = CboUser rng.Offset(0, 8).value = TxtLoggedInAs rng.Offset(0, 9).value = TxtReason rng.Offset(0, 10).value = CboAdmin rng.Offset(0, 13).value = TxtRemarks Next i End With MsgBox "Record updated successfully", vbOKOnly, "Database - Admin Dashboard" Unload Me UsrFrmAdminDashboard.Show End Sub

Thanks in advance.

 

[DanteAmor]

Try this

Private Sub CmdSubmit_Click()
  Dim wbk As Workbook, i As Long, sh As Worksheet, f As Range
  
  Set wbk = Workbooks.Open("\\XXXX\Returns v.2.0.xlsx", WriteResPassword:="XXXX", IgnoreReadOnlyRecommended:=True)
  Set sh = wbk.Sheets("Admin Users")
  Set f = sh.Range("A:A").Find(TxtPIN.Value, , xlValues, xlWhole)
  f.Offset(0, 7).Value = CboUser
  f.Offset(0, 8).Value = TxtLoggedInAs
  f.Offset(0, 9).Value = TxtReason
  f.Offset(0, 10).Value = CboAdmin
  f.Offset(0, 13).Value = TxtRemarks
  MsgBox "Record updated successfully", vbOKOnly, "Database - Admin Dashboard"
End Sub

 

[K1600]

Absolutely spot on, thank you kindly Dante!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.