Serching for duplicates - replacing with new information

[Excel Rookie]

Hi,

I presently have coding that allow a person to complete a UserForm and the data is moved to a sheet. Individuals may submit submit numerous request via The UserForm throughout the year in order to update their info so I need a macro to search the sheet for duplicate PRI's, remove that info and replace with the new information.

this is my present coding:

Private Sub CmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim r As Range
Dim info, rw As Range, n As Long
 
Const strPwd As String = "Transfer19"
ThisWorkbook.Unprotect Password:=strPwd
Set ws = Worksheets("Inventory")
If Trim(Me.TxtFirst.Value) = "" Then
  Me.TxtFirst.SetFocus
  MsgBox "Please complete First Name field"
  Exit Sub
End If
If Trim(Me.TxtLast.Value) = "" Then
  Me.TxtFirst.SetFocus
  MsgBox "Please complete Last Name field"
  Exit Sub
End If
If Trim(Me.TxtPRI.Value) = "" Then
  Me.TxtFirst.SetFocus
  MsgBox "Please complete the PRI field"
  Exit Sub
End If
If Trim(Me.TxtLinguistic.Value) = "" Then
  Me.TxtFirst.SetFocus
  MsgBox "Please select a linguistic choice"
  Exit Sub
End If
If Trim(Me.TxtEmail.Value) = "" Then
  Me.TxtFirst.SetFocus
  MsgBox "Please insert your Email address"
  Exit Sub
End If
If Trim(Me.ListProv1.Value) = "" Then
  Me.TxtFirst.SetFocus
  MsgBox "Please select a Province"
  Exit Sub
End If
If Trim(Me.ListCity1.Value) = "" Then
  Me.TxtFirst.SetFocus
  MsgBox "Please select a City"
  Exit Sub
End If
If Trim(Me.TxtResumeNum.Value) = "" Then
  Me.TxtFirst.SetFocus
  MsgBox "Please provide us with the RDIMS# to your resume"
  Exit Sub
End If
If Trim(Me.TxtDate.Value) = "" Then
  Me.TxtFirst.SetFocus
  MsgBox "Please insert your registration date"
  Exit Sub
End If
If Trim(Me.TxtGR.Value) = "" Then
  Me.TxtFirst.SetFocus
  MsgBox "Please insert Substantive Group"
  Exit Sub
End If
If Trim(Me.TxtLV.Value) = "" Then
  Me.TxtFirst.SetFocus
  MsgBox "Please insert Substantive Level"
  Exit Sub
End If
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
'searching for duplicates
    Set r = ws.Range("C:C").Find(Me.TxtPRI.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not r Is Nothing Then
        MsgBox "You already have a record in the National Transfer Inventory. The previous record will be deleted and the new data entered in its place"
        .EntireRow.Delete
    End If
    

 With ws
    'get all the tombstone info into an array
    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"
 'get the first empty row...
    Set rw = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow
   
    'loop over the province and city controls
    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                   '<<if on first loop, or province has been selected
            rw.Cells(1).Resize(1, 10).Value = info '<< populate all common 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

 

[Pecoflyer]

Hi,
When posting code, please wrap it with code tags ( Edit code - select code - click the #button.)
It keeps the macro's structure and makes it easy to copy and handle.
Thank you

I did it for you this time

 

[Excel Rookie]

thanks but I am getting an error message:

Compile Error
Invalid or unqualified reference

for the line: .EntireRow.Delete

 

[NoS]

that error is because it would need to be

 r.EntireRow.Delete

but I'm skeptical that this is really what you're trying to achieve.

The thread title says Serching for duplicates - replacing with new information
Your code doesn't search for duplicates, it only searches for the first instance of PRI in column C.
If, in fact there will only be one instance, and assuming your form gives the Province and City,
I can't see why you couldn't overwrite the existing record without deleting that line and perhaps without the n=1 to 10 loop.

 

[Excel Rookie]

Searching for duplicates - replacing with new information

Thanks....a person can submit up to 10 options for province and city. If they select 10 combinations then there will be 10 rows with the employee's PRI.

If at a later date they want to update their info or change some of their options, they submit another form. At that time, I need search to happen and the rows with the matching PRI deleted and the new information entered. The information does not need to go in the same place but can be added at the end of the sheet.

 

[NoS]

Sorry, I don't understand how you intend to handle the Province and City options.
Any chance of you attaching an example sample file indicative of what you'd be dealing with ?

 

[Pecoflyer]

@rookie
please acknowledge having read and understood post #2
Thanks

 

[Excel Rookie]

@Pecoflyer
I do not understand what you mean by "wrap it with code tags ( Edit code - select code - click the #button.)" or even how to do that. I usually just copy paste from my sheet

 

[Excel Rookie]

@NoS
I created a UserForm where the person enters their First, Last name, PRI,GR, LV, Linguistic Profile, etc.....

once that is done, they then proceed with selecting the location they would like to Transfer to. I created a number of ComboBoxes with drop down lists that they can select from (ListProv1 and ListCity1 - if they select BC then only cities found in BC will be in the drop down) there are 10 Prov. and 10 City ComboBoxes number 1 to 10 where Prov1 is linked to City1, Prov2 linked to City2

the person can update their info at any time so I require the list to be updated every time they submit a completed UserForm. The old information needs to be removed or overwritten and the new inserted into the Sheet.

The present code I have enters the new information correctly but does not remove the old information and the option of "r.EntireRow.Delete" gives me an error message as well

 

[Pecoflyer]

When you copy paste your code in the reply window, select the code, then click the #button on the reply toolbar.
Instead of occupying lots of space in the thread, your code is now in a special window with scroll bars. This way, the thread is much easier to follow.
Is this clear now or do I detect some unwillingness to help us keep the forum as easy as possible?

 

[Excel Rookie]

awesome ....thank you

no issue doing that just didn't know how

 

[NoS]

Perhaps delete the rows by filtering for the PRI

    lr = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    
    With ws.Range("C1:C" & lr)
        'remove any existing filters
        .AutoFilter
        'filter for PRI
        .AutoFilter field:=1, Criteria1:=Me.TxtPRI.Value
        'delete these rows
        .Offset(1).Resize(lr - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        'remove any existing filters
        .AutoFilter
    End With

 

[Excel Rookie]

@NoS

thanks but I am getting an error message on the AutoFilter lines

 

[NoS]

I don't when I test it on what I have.
Your choice whether or not to provide a workbook indicative of what you have.

 

[Excel Rookie]

how do I provide a workbook indicative? Is this what you are looking for?

so if John Smith submits a subsequent form - the previous line will be deleted and the new information inserted and the end of the sheet

First Name	Last Name	PRI	GR	LV
John	Smith	12345689	AS	05
Jane	Doe	23656987	CR	05

 

[Pecoflyer]

To attach a sheet read the FAQ and search for the word "attach". That is the purpose of a FAQ tool

 

[Excel Rookie]

searching for duplicates - replacing with new information

I have attached a copy of the Workbook that I am using

If an individual enters a subsequent entry with the same PRI identifier, I require the previous information to be deleted and the new information inserted and the end of the sheet

 

[NoS]

Sorry, I'm unable to help you as I can't get to the Inventory sheet.

 

[NoS]

Your original post indicated use of .Find which would be faster than stepping up the C column cell by cell to find the rows with the specific PRI.
Had you already gone that route and found it too slow ?

If not you could try

'  
'    check form for missing info first   
'
 With ws
    .Unprotect Password:="Transfer19"
    
    lr = .Range("C" & Rows.Count).End(xlUp).Row
    
    For i = lr To 1 Step -1
        If .Cells(i, "C") = Me.TxtPRI Then .Rows(i).Delete
    Next i
    
    'get all the tombstone info into an array
    ' 
    ' etc
    '

 

[Excel Rookie]

Searching for duplicates - replacing with new information

@NoS

I inserted the code and the new information does populate the Inventory Sheet but the old info does not delete. I am resending the attachment but removed the password protection to the document which should give access to the Inventory sheet