Using VBA code to prevent a button from making duplicate entries

[CronoExcell]

I am trying to crate an inventory tracking spreadsheet. I have made a button that moves entered information to another sheet.
The button takes the information in cells A6:F:6 individually and applies the information entered to a list of parts on another page.
The code I am using to do this is as follows:
Sub Button4_Click()
Dim Part as String, Description As String, Quantity As String, Location As String
Worksheets ("Data Entry") .Select
Part = Range ("A6")
Description = Range ("B6")
Needed = Range ("C6")
Quantity = Range ("D6")
Location = Range ("F6")
Vender = Range ("G6")
Worksheets ("Parts") .Select
If Worksheets ("Parts") .Range ("A1") .Offset (1,0) <> "" Then
Worksheets ("Parts") .Range ("A1") .End(xlDown) .Select
End If
ActiveCell.Offest (1,0) .Select
ActiveCell.Value = Part

ActiveCell.Offest (0,1) .Select
ActiveCell.Value = Needed​

ActiveCell.Offest (0,1) .Select
ActiveCell.Value = Quantity​

ActiveCell.Offest (0,4) .Select
ActiveCell.Value = Description​

ActiveCell.Offest (0,-2) .Select
ActiveCell.Value = Location​

ActiveCell.Offest (0,3) .Select
ActiveCell.Value = Vender
Worksheets ("Data Entry") .Select
Worksheets ("Data Entry") .Range ("A2") .Select
End Sub

This works except I need it to not put the same part on the list Twice. I need it to scan the column A on the Parts page and see if the number in cell A6 on the Data entry page is already there if it is I would like a msgbox saying "Part Number already exists"
I am self taught so there may be something simple to fix this but the things I tried (data validation and the like) do not stop the button from putting the same entry into the list twice.
Thanks for your help.
Crono
​

 

[mumps]

Can you post a screen shot of what your data on the 2 sheets looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[dmt32]

Hi,welcome to forum

Try this update to your code

Sub Button4_Click()
    Dim DataEntryRange As Range, Cell As Range
    Dim m As Variant
    Dim Search As String
    Dim LastRow As Long, c As Long
    Dim wsParts As Worksheet
    
    
    With ThisWorkbook
        Set wsParts = .Worksheets("Parts")
        Set DataEntryRange = .Worksheets("Data Entry").Range("A6,C6,D6,F6,B6,G6")
    End With
    
'get part no
    Search = DataEntryRange.Cells(1, 1).Value
    
'check for existing record
    m = Application.Match(Search, wsParts.Columns(1), 0)
    
    If IsError(m) Then
'add new record
        c = 1
        LastRow = wsParts.Cells(wsParts.Rows.Count, "A").End(xlUp).Row + 1
        For Each Cell In DataEntryRange.Cells
            wsParts.Cells(LastRow, c).Value = Cell.Value
            c = c + 1
            c = IIf(c = 4, 5, IIf(c = 6, 7, c))
        Next
    Else
'inform user
        MsgBox Search & Chr(10) & "Part Number already exists", 48, "Part Exists"
    End If
End Sub

Dave

 

[CronoExcell]

Dave,
This worked perfectly. Will have to check up on the functions you used to see how they work. Thank you so much.

 

[dmt32]

Dave,
This worked perfectly. Will have to check up on the functions you used to see how they work. Thank you so much.

Most welcome glad solution helped you

Dave