Userform inputs to overwrite entries if they're duplicates

Nikeyg

New Member
Joined
Jan 22, 2018
Messages
11
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

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?
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,469
Office Version
2013
Platform
Windows
Hi,
Assuming that each entry in Column 1 is unique then try this update to your code


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

New Member
Joined
Jan 22, 2018
Messages
11
You are a legend!
Hi,
Assuming that each entry in Column 1 is unique then try this update to your code


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
 

Forum statistics

Threads
1,089,270
Messages
5,407,306
Members
403,132
Latest member
Black_Mamba_1666

This Week's Hot Topics

Top