number not found ... error proofing vba macro

[lostinvb]

The following code takes wsInvoice (A1 and down the column) searches wsItems for the number and returns the price when found.

everything works great until the number it is searching for isn't found.

How can I .. if number not found put "not found" in the price cell in wsInvoice.

Private Sub CommandButton1_Click()


    Dim wsItems As Worksheet, wsInvoice As Worksheet
    Dim iInvoice As Long, iLastInvoice As Long
    Dim iPrice As Long
    
    
    
    Application.ScreenUpdating = False
    
    Set wsItems = Worksheets("Items")
    Set wsInvoice = Worksheets("Invoice")
    
    iLastInvoice = wsInvoice.Cells(wsInvoice.Rows.Count, 1).End(xlUp).Row
    
    For iInvoice = 1 To iLastInvoice
        iPrice = 0
        
        On Error Resume Next    '   in case not found
        iPrice = Application.WorksheetFunction.Match(wsInvoice.Cells(iInvoice, 1).Value, wsItems.Columns(1), 0)
        On Error GoTo 0
        
        wsInvoice.Cells(iInvoice, 8).Value = wsItems.Cells(iPrice, 8).Value
        
    Next iInvoice
    
    Application.ScreenUpdating = True

[大灰狼1976]

Hi lostinvb!
you can use set range=range.find(......), then not need on error.

Private Sub CommandButton1_Click()




    Dim wsItems As Worksheet, wsInvoice As Worksheet
    Dim iInvoice As Long, iLastInvoice As Long
    Dim iPrice As Long, Rng As Range
    
    
    
    Application.ScreenUpdating = False
    
    Set wsItems = Worksheets("Items")
    Set wsInvoice = Worksheets("Invoice")
    
    iLastInvoice = wsInvoice.Cells(wsInvoice.Rows.Count, 1).End(xlUp).Row
    
    For iInvoice = 1 To iLastInvoice
        Set Rng = wsItems.Columns(1).Find(wsInvoice.Cells(iInvoice, 1).Value, lookat:=xlWhole)
        If Rng Is Nothing Then    '   in case not found
            wsInvoice.Cells(iInvoice, 8).Value = "not found"
        Else
            wsInvoice.Cells(iInvoice, 8).Value = wsItems.Cells(Rng.Row, 8).Value
        End If
    Next iInvoice
    
    Application.ScreenUpdating = True

[Paul_Hossler]

Ref post4 on
http://www.vbaexpress.com/forum/showthread.php?64727-find-and-retrieve-a-price-from-a-different-sheet-index-match-vlookup&p=388703&highlight=#post388703

I originally left it blank, but you can easily add "Not Found"

My (very) personally style is to use a 'flag' value (iPrice = 0) and if there is an error, it is still set
Many ways to handle a lookup not found; that's just the one I like to use

Private Sub CommandButton1_Click()
    Dim wsItems As Worksheet, wsInvoice As Worksheet
    Dim iInvoice As Long, iLastInvoice As Long
    Dim iPrice As Long
    
    Application.ScreenUpdating = False
    
    Set wsItems = Worksheets("Items")
    Set wsInvoice = Worksheets("Invoice")
    
    iLastInvoice = wsInvoice.Cells(wsInvoice.Rows.Count, 1).End(xlUp).Row
    
    For iInvoice = 1 To iLastInvoice
        iPrice = 0
        
        On Error Resume Next    '   in case not found
        iPrice = Application.WorksheetFunction.Match(wsInvoice.Cells(iInvoice, 1).Value, wsItems.Columns(1), 0)
        On Error GoTo 0
    
     If iPrice > 0 then    
            wsInvoice.Cells(iInvoice, 8).Value = wsItems.Cells(iPrice, 8).Value
     Else
            wsInvoice.Cells(iInvoice, 8).Value = "Not Found"
     End If
        
    Next iInvoice
    
    Application.ScreenUpdating = True
End SUb

Another way - Assume it's "Not Found" at first and replace that with the looked-up price

Private Sub CommandButton1_Click()
    Dim wsItems As Worksheet, wsInvoice As Worksheet
    Dim iInvoice As Long, iLastInvoice As Long
    Dim iPrice As Long
    
    Application.ScreenUpdating = False
    
    Set wsItems = Worksheets("Items")
    Set wsInvoice = Worksheets("Invoice")
    
    iLastInvoice = wsInvoice.Cells(wsInvoice.Rows.Count, 1).End(xlUp).Row
    
    For iInvoice = 1 To iLastInvoice
        wsInvoice.Cells(iInvoice, 8).Value = "Not Found"
        iPrice = 0
        
        On Error Resume Next    '   in case not found
        iPrice = Application.WorksheetFunction.Match(wsInvoice.Cells(iInvoice, 1).Value, wsItems.Columns(1), 0)
        On Error GoTo 0
    
 If iPrice > 0 then    
            wsInvoice.Cells(iInvoice, 8).Value = wsItems.Cells(iPrice, 8).Value
 End If
        
    Next iInvoice
    
    Application.ScreenUpdating = True
End SUb

[lostinvb]

GREAT! All three work perfectly. thanks guys