User form Error

[johnohio]

I am trying to create a user form & I am stuck.

I get this error:
Run-time error '5'
invalid procedure call or argument

Here is the code

rivate Sub CommandButton1_Click()
'submit button to add new customer

Dim xxx As Worksheet
Set xxx = Worksheets("cmast")
lastrow = xxx.Range("a" & Rows.Count).End(xlUp).Row

'Cust # and  Name Entered
If Me.Custnum = "" Or Me.custname = "" Then
    MsgBox ("You must enter a Customer Number and Customer Name")
    Exit Sub
    End If

' Check Cust # does not exist
If WorksheetFunction.Match(Me.Custnum, xxx.Cells("A:A"), 0) = "" Then ' <<<this line is the issue
    MsgBox ("it worked")
    End If
   
End Sub

Thanks for the help!

 

[MARK858]

Cells("A:A") isn't valid syntax, you probably mean Range("A:A")

 

[johnohio]

Mark,
I tried doing what you suggest but now I have a different error message.......

 

[MARK858]

That error just means it isn't finding a match. If you are trying to trap the error when a result is not found then you are better off using Application.Match.

 

[johnohio]

I had to change some things and now I am getting another error.
I am getting a 424 run time error.

Thanks Again for any help....

Private Sub CommandButton1_Click()
'submit button to add new customer

Dim xxx As Worksheet
Set xxx = Worksheets("cmast")
Dim zz As Worksheet
Set zz = Worksheets("z")

lastrow = xxx.Range("a" & Rows.Count).End(xlUp).Row


'CheckCust # and  Name Entered
If Me.Custnum = "" Or Me.custname = "" Then
    MsgBox ("You must enter a Customer Number and Customer Name")
    Exit Sub
    End If
 
' Check Cust # is not assigned
If Application.Match(Me.Custnum, xxx.Range("A:A"), 0) Is Error Then '<<<<<< problem
        q = 1
    Else
       MsgBox ("Customer number already exists")
       Exit Sub
    End If
   
   
End Sub

 

[MARK858]

Try...

    If Not IsError(Application.Match(Me.Custnum, xxx.Range("A:A"), 0)) Then
        MsgBox ("Customer number already exists")
        Exit Sub
    Else
        q = 1
    End If

 

[Norie]

Try this.

Private Sub CommandButton1_Click()
'submit button to add new customer
Dim xxx As Worksheet
Dim zz As Worksheet
Dim Res As Variant

    Set zz = Worksheets("z")
    Set xxx = Worksheets("cmast")
    
    lastrow = xxx.Range("a" & Rows.Count).End(xlUp).Row

    
    'CheckCust # and  Name Entered
    If Me.Custnum = "" Or Me.custname = "" Then
        MsgBox ("You must enter a Customer Number and Customer Name")
        Exit Sub
    End If
     
    Res = Application.Match(Me.Custnum, xxx.Range("A:A"), 0)
    ' Check Cust # is not assigned
    If IsError(Res) Then
        q = 1
    Else
        MsgBox ("Customer number already exists")
        Exit Sub
    End If
      
End Sub

Note, it might be worth checking what type of data you have in the range you are searching in - if it's numeric then you aren't going to get a match for the text value from the controls of the userform.

 

[johnohio]

Got it working now. Thanks to both of you.
(and thanks for the reminder about data types!)