Userform skip blank cells while searching for duplicate entries

[hey1000]

Hi all,
First let me start by saying thank you to everyone on this forum. The time and effort that everyone here puts in to help out is amazing. This truly is a great website!

I am (what i used to think) an experienced excel user. But wow was i wrong. Last week i bit off more than i can chew at work and have begun the process of learning VBA. I have researched what i am trying to accomplish and have compiled different ideas and different code to arrange, search, print and update inventory at work.

I have begun the process of putting it all together into one master spreadsheet. It has been going very well, with of course everyones help here. I have hit a roadblock. I am stuck on the add button. I have a few if statements in the code that checks to see if there is an exsisting id or serial number already stored in the data worksheet. If it does a msgbox appears and tells the user there is a matching id/serial # and to please update instead of add. The problem is this, every item on the list will have a serial # BUT not every item will have an ID number. With the code i was able to find it searches and works as wanted except if there is no id number on file and no id number inputed on the userform then it reports a "match" based on the empty cells. Is there anyway to have the code overlook the empty cells? and if so how. I know i will have more questions along the way, i am just in the beginning stage of putting code together for this userform and i would like it to be able to do alot.

Thank you all for your help .
K

[royUK]

This might be some help

Private Sub cmbAdd_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Dim rCl As Range


    Set ws = Worksheets("Data")


    'find first empty row in database
    iRow = ws.Cells(Rows.Count, 1) _
           .End(xlUp).Offset(1, 0).Row

    'check for a Group
    If Trim(UserForm1.ComboBoxgroup.Value) = "" Then
        UserForm1.ComboBoxgroup.SetFocus
        MsgBox "Please select an Inventory Group"
        Exit Sub
    End If

    With ActiveSheet.UsedRange
        Set rCl = .Find(Me.TextBoxserial.Value, LookIn:=xlValues)
        'looks for serial& checks adjacent cell for ID
        If Not rCl Is Nothing And IsEmpty(rCl.Offset(0, 1)) Then
            MsgBox "Serial number " & Me.TextBoxserial.Value & "found. No ID# entered"
            GoTo do_something
            'check for ID selected on form
        Else: Set rCl = .Find(Me.TextBoxid.Value, LookIn:=xlValues)
            If Not rCl Is Nothing Then
                MsgBox "Id# used for  " & rCl.Offset(0, -1)
            End If
        End With
do_something:
        'code
    End Sub

You wouldn't need the check if you used ComboBoxes for the IDs & Serial numbers populated by existing numbers

[hey1000]

Roy,

Thank you for your response. I have tried the code you provided and i was unable to get that to work. I am probably doing something wrong with it. You are right about using comboboxes. That would be helpful but i am trying to allow the user to be able to add, delete ammend records. If they are adding a record and there is no id required for that specific item or if there is an nypd required they would not be able to type in that new id.

what do you think about using a msgbox if that field is left blank saying something like "is there an id required?" and using yes or no and then capturing those values. Then populating the id field with an N/A. so that this way there are no blank cells? I was trying to figure that out last night but was having difficulty with that solution as well.

Again, thank you for your time and hard work

k