searching for duplicates and replacing with the new information


Board Regular
Jul 31, 2018
My present coding moves information completed on a UserForm to a Spreadsheet. Individuals may need to update the information submitted so if they submit another form, I require a search to happen by the PRI number and replace the old information with the new information. For example.... If the initial submission had 4 options but the new submissions has 6 options then the previous 4 are deleted and replaced by the new 6 options but the additional options must go in the next empty row and not overwrite another PRI

this is my present coding:

Private Sub CmdAdd_Click()
Dim ws As Worksheet
Dim info, rw As Range, n As Long, r As Range

Const strPwd As String = "Transfer19"

ThisWorkbook.Unprotect Password:=strPwd

Set ws = Worksheets("Inventory")

With ws

info = Array(Me.TxtFirst.Value, Me.TxtLast.Value, _
Me.TxtPRI.Value, Me.TxtGR.Value, _
Me.TxtLV.Value, Me.TxtLinguistic.Value, _
Me.TxtEmail.Value, Me.TxtResumeNum.Value, _
Me.TxtReason.Value, Me.TxtDate.Value)

.Unprotect Password:="Transfer19"

Set rw = .Cells(Rows.Count, 1).End(x1Up).Offset(1, 0).EntireRow

For n = 1 To 10
'get province and city values
p = Me.Controls("ListProv" & n).Value
c = Me.Controls("ListCity" & n).Value

If n = 1 Or p <> "" Then
rw.Cells(1).Resize(1, 10).Value = info
rw.Cells(11).Value = p
rw.Cells(12).Value = c
Set rw = rw.Offset(1, 0) 'move down one row
End If
Next n

.Protect Password:="Transfer19"

End With

ThisWorkbook.Protect Password:=strPwd


End Sub

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.


Board Regular
Jul 31, 2018
Is there anyone that can help me with my issue? Any help would be appreciated. I have tried a number of options and read thru previous posts on deletion of rows but with my limited knowledge, I am unable to adapt the coding to my situation. I am also open to a solution that would just delete rows with matching PRI's and adding the info at the bottom of the sheet instead of replacing on the same lines.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics