searching for duplicates and replacing with the new information

Blanchetdb

Board Regular
Joined
Jul 31, 2018
Messages
153
Office Version
  1. 2016
Platform
  1. Windows
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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

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
Back
Top