searching for duplicates and replacing with the new information

Blanchetdb

Board Regular
Joined
Jul 31, 2018
Messages
74
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



ThisWorkbook.Save



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.

Blanchetdb

Board Regular
Joined
Jul 31, 2018
Messages
74
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

Threads
1,108,809
Messages
5,525,002
Members
409,615
Latest member
papaluigi94

This Week's Hot Topics

Top