Userform: Retrieve data from excel sheet and overwrite with new data in the same rows

Hello,

I am new to VBA, I have created a userform with multipage, has two page that help to add new record in a excel sheet. Data has a unique reference no.(TxtRef.Value) for each record. I am trying to add a button to load the added data for a specific record using reference no back to userform so that it can be updated and overwrite back to the sheet in the same row, So far it is adding new record correctly. I do not know how to populate all the fields of the of an existing record and overwrite it back to the same row instead of adding a new record. Below is my codes

I thank you in advance for your time and effort.

Thanks,
Pbawal

Private Sub CommandButton1_Click()

Dim Lastrow As Long

Lastrow = Worksheets("Audit Data").Range("B1048576").End(xlUp).Row + 1

'FOR RECORD TAB
Worksheets("Audit Data").Range("A" & Lastrow).Value = CmboAuditor.Value
Worksheets("Audit Data").Range("B" & Lastrow).Value = CmboAssociate.Value
Worksheets("Audit Data").Range("C" & Lastrow).Value = txtRecord.Value
Worksheets("Audit Data").Range("D" & Lastrow).Value = TxtRef.Value
Worksheets("Audit Data").Range("E" & Lastrow).Value = CmboOffice.Value
Worksheets("Audit Data").Range("F" & Lastrow).Value = TxtWeek.Value
Worksheets("Audit Data").Range("G" & Lastrow).Value = TxtDate.Value
Worksheets("Audit Data").Range("H" & Lastrow).Value = CmboPGroup.Value
Worksheets("Audit Data").Range("CT" & Lastrow).Value = CmboProcess.Value

'FOR PERSON TAB

If CheckBox1.Value = True Then
Worksheets("Audit Data").Range("I" & Lastrow).Value = "Y"
ElseIf CmboMr.Value <> "" Then
Worksheets("Audit Data").Range("I" & Lastrow).Value = CmboMr.Value
Else
Worksheets("Audit Data").Range("I" & Lastrow).Value = "NA"
End If

If CheckBox2.Value = True Then
Worksheets("Audit Data").Range("J" & Lastrow).Value = "Y"
ElseIf CmboTitle.Value <> "" Then
Worksheets("Audit Data").Range("J" & Lastrow).Value = CmboTitle.Value
Else
Worksheets("Audit Data").Range("J" & Lastrow).Value = "NA"
End If

If CheckBox3.Value = True Then
Worksheets("Audit Data").Range("K" & Lastrow).Value = "Y"
ElseIf CmboPeople.Value <> "" Then
Worksheets("Audit Data").Range("K" & Lastrow).Value = CmboPeople.Value
Else
Worksheets("Audit Data").Range("K" & Lastrow).Value = "NA"
End If

If CheckBox4.Value = True Then
Worksheets("Audit Data").Range("L" & Lastrow).Value = "Y"
ElseIf CmboPplkno.Value <> "" Then
Worksheets("Audit Data").Range("L" & Lastrow).Value = CmboPplkno.Value
Else
Worksheets("Audit Data").Range("L" & Lastrow).Value = "NA"
End If

If CheckBox5.Value = True Then
Worksheets("Audit Data").Range("M" & Lastrow).Value = "Y"
ElseIf CmboAddressed.Value <> "" Then
Worksheets("Audit Data").Range("M" & Lastrow).Value = CmboAddressed.Value
Else
Worksheets("Audit Data").Range("M" & Lastrow).Value = "NA"
End If

If CheckBox6.Value = True Then
Worksheets("Audit Data").Range("N" & Lastrow).Value = "Y"
ElseIf CmboEmail.Value <> "" Then
Worksheets("Audit Data").Range("N" & Lastrow).Value = CmboEmail.Value
Else
Worksheets("Audit Data").Range("N" & Lastrow).Value = "NA"
End If

If CheckBox7.Value = True Then
Worksheets("Audit Data").Range("O" & Lastrow).Value = "Y"
ElseIf CmboPplphon.Value <> "" Then
Worksheets("Audit Data").Range("O" & Lastrow).Value = CmboPplphon.Value
Else
Worksheets("Audit Data").Range("O" & Lastrow).Value = "NA"
End If

' Blank all the fields

'Record


CmboAuditor.Value = ""
CmboAssociate.Value = ""
txtRecord.Value = ""
TxtRef.Value = ""
CmboOffice.Value = ""
TxtWeek.Value = ""
TxtDate.Value = ""
CmboPGroup.Value = ""

'Person
CmboMr.Value = ""
CmboTitle.Value = ""
CmboPeople.Value = ""
CmboPplkno.Value = ""
CmboAddressed.Value = ""
CmboEmail.Value = ""
CmboPplphon.Value = ""

' Person
CheckBox1.Value = False
CheckBox2.Value = False
CheckBox3.Value = False
CheckBox4.Value = False
CheckBox5.Value = False
CheckBox6.Value = False
CheckBox7.Value = False
End Sub