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