+ Reply to Thread
Results 1 to 2 of 2

Error checking

Hybrid View

  1. #1
    Registered User
    Join Date
    07-22-2010
    Location
    USA
    MS-Off Ver
    2010
    Posts
    24

    Error checking

    Since error 1004 is not trappable (or not in the list of trappable errors), How do I check to see if val is in the named range price?
    This is the line I am having problems with.

    If WorksheetFunction.IsError(WorksheetFunction.VLookup(val, Range("price"), 2, False)) = True Then

    Private Sub Itemnum_AfterUpdate()
    
    On Error Resume Next
    
    Dim val As String
    With userforms1
     
    
        val = Itemnum.Value
    
        'check to see if val is in range price
        'if not reset
        If WorksheetFunction.IsError(WorksheetFunction.VLookup(val, Range("price"), 2, False)) = True Then
            Itemnum.Value = ""
            MsgBox ("You have entered an invalid Item Number, Please try again.")
            Itemnum.SetFocus
            Exit Sub
        Else
            'populate descrption
            desc.Value = WorksheetFunction.VLookup(val, Range("price"), 2, False)
        End If
        '  get priceing
        If tog.Value = "True" Then
            price.Value = Format(WorksheetFunction.VLookup(val, Range("price"), 6, False), dformat)
        Else
            price.Value = Format(WorksheetFunction.VLookup(val, Range("price"), 5, False), dformat)
        End If
    
    End With
    End Sub
    Attached Files Attached Files
    Last edited by Cbrehm; 08-17-2010 at 07:39 AM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Error checking

    Maybe like this:
    Private Sub Itemnum_AfterUpdate()
        Dim val         As String
        
        With userforms1
            val = .Itemnum.Value
            If IsError(Application.Match(val, Range("price").Columns(1), 0)) Then
                MsgBox ("Invalid Item Number!")
                .Itemnum.SetFocus
                Exit Sub
            Else
                .desc.Value = WorksheetFunction.VLookup(val, Range("price"), 2, False)
            End If
            
            ' ...
        End With
    End Sub
    Entia non sunt multiplicanda sine necessitate

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1