searching for duplicates and replacing with the new information

Blanchetdb

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

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Blanchetdb

Board Regular
Joined
Jul 31, 2018
Messages
83
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,826
Messages
5,855,875
Members
431,771
Latest member
CoryMelth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top