Userform inputs to overwrite entries if they're duplicates

[Nikeyg]

I have a spreadsheet with a userform used to add entries to a mapping table of items. There are four fields which need to be input and once done the entries are appended to the bottom of a table.
What I need to do is get the code that inputs the data to look for a duplicate entry for the first entry and if it finds one to then overwrite it instead of append to the bottom.
Here's my existing code

Private Sub cmdAdd_Click()    'Copy input values to sheet.
    Dim lRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Contract mapping")
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
        .Cells(lRow, 1).Value = Me.MACCode.Value
        .Cells(lRow, 2).Value = Me.OurCode.Value
        .Cells(lRow, 3).Value = Me.CallPutFuture.Value
        .Cells(lRow, 4).Value = Me.Multiplier.Value


    End With
    'Clear input controls.
    Me.MACCode.Value = ""
    Me.OurCode.Value = ""
    Me.CallPutFuture.Value = ""
    Me.Multiplier.Value = ""


End Sub

Is this possible?

 

[dmt32]

Hi,
Assuming that each entry in Column 1 is unique then try this update to your code

Private Sub cmdAdd_Click()
'Copy input values to sheet.
    Dim lRow As Long
    Dim Search As Variant, m As Variant
    Dim msg As String
    Dim ws As Worksheet
    
    
    Set ws = ThisWorkbook.Worksheets("Contract mapping")
    
    Search = Me.MACCode.Value
'exit if entry empty
    If Len(Search) = 0 Then Exit Sub
'if number only coerce to numeric
    If IsNumeric(Search) Then Search = Val(Search)


'get last row + 1
    lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1, 0).Row


'check for match in Column 1
     m = Application.Match(Search, ws.Columns(1), 0)
'if match change lRow variable
     If Not IsError(m) Then lRow = CLng(m)


'add / update record
    With ws
        .Cells(lRow, 1).Value = Me.MACCode.Value
        .Cells(lRow, 2).Value = Me.OurCode.Value
        .Cells(lRow, 3).Value = Me.CallPutFuture.Value
        .Cells(lRow, 4).Value = Me.Multiplier.Value
    End With
    
'Clear input controls.
    Me.MACCode.Value = ""
    Me.OurCode.Value = ""
    Me.CallPutFuture.Value = ""
    Me.Multiplier.Value = ""


'inform user
    msg = IIf(IsError(m), "New Record Added", "Record Updated")
    
    MsgBox msg, 48, msg


End Sub

Hope Helpful

Dave

 

[Nikeyg]

I'm currently trying to bend this tutorial to my will with no luck
https://www.excel-easy.com/vba/examples/interactive-userform.html

I have a feeling the answer is in there somewhere, but everything I try keeps falling over.

 

[Nikeyg]

You are a legend!

Hi,
Assuming that each entry in Column 1 is unique then try this update to your code

Private Sub cmdAdd_Click()
'Copy input values to sheet.
    Dim lRow As Long
    Dim Search As Variant, m As Variant
    Dim msg As String
    Dim ws As Worksheet
    
    
    Set ws = ThisWorkbook.Worksheets("Contract mapping")
    
    Search = Me.MACCode.Value
'exit if entry empty
    If Len(Search) = 0 Then Exit Sub
'if number only coerce to numeric
    If IsNumeric(Search) Then Search = Val(Search)


'get last row + 1
    lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1, 0).Row


'check for match in Column 1
     m = Application.Match(Search, ws.Columns(1), 0)
'if match change lRow variable
     If Not IsError(m) Then lRow = CLng(m)


'add / update record
    With ws
        .Cells(lRow, 1).Value = Me.MACCode.Value
        .Cells(lRow, 2).Value = Me.OurCode.Value
        .Cells(lRow, 3).Value = Me.CallPutFuture.Value
        .Cells(lRow, 4).Value = Me.Multiplier.Value
    End With
    
'Clear input controls.
    Me.MACCode.Value = ""
    Me.OurCode.Value = ""
    Me.CallPutFuture.Value = ""
    Me.Multiplier.Value = ""


'inform user
    msg = IIf(IsError(m), "New Record Added", "Record Updated")
    
    MsgBox msg, 48, msg


End Sub

Hope Helpful

Dave

 

[dmt32]

You are a legend!

I take it this means solution does what you want?


Dave

 

[Nikeyg]

It does, yes.
Thank you very much.

 

[dmt32]

It does, yes.
Thank you very much.

Most welcome glad I was able to assist.

Many thanks for feedback

Dave

 

[MrDrock]

Hey @dmt32

I'm trying to kind of mimic the same thing, but through the Microsoft Forms. Where the user enters data through a form and then the data is populated in an excel table. I want the excel table to automatically update if there was a duplicate entered say a couple weeks or months later. Can this be done with coding in VBA?

Green "J" column = My "Unique ID"
Yellow highlight = new entry with same unique ID as the last entry ( but i want it to replace "OLD" entry with new updated data automatically )

 

[Joe4]

Welcome to the Board!

I'm trying to kind of mimic the same thing, but through the Microsoft Forms.

If you are using Microsoft Forms (and trying to integrate it with Excel), then it is best to post your question to a brand new thread, especially since the original question has nothing to do with Microsoft Forms.