Vlookup Error handling
 

I am using the following to get information from Sheet1 to Sheet2.

Sub Getdata()
msg = "Do you want to filter the data now?"
Style = vbYesNo + vbDefaultButton1 + vbQuestion
Title = "Filter"
response = MsgBox(msg, Style, Title)
If response = vbYes Then
Application.ScreenUpdating = False
With Worksheets("Sheet2")
Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
With Worksheets("Sheet1")
Set rng1 = .Range(.Cells(2, 3), .Cells(Rows.Count, 3).End(xlUp))
End With
For Each cell In rng
cell.Offset(0, 2).Value = Application.VLookup(cell.Value, rng1.Resize(, 10),
7, 0)
cell.Offset(0, 3).Value = Application.VLookup(cell.Value, rng1.Resize(, 10),
8, 0)
cell.Offset(0, 4).Value = Application.VLookup(cell.Value, rng1.Resize(, 10),
9, 0)
Next cell
Application.ScreenUpdating = True
End If
End Sub

On some occasions values in rng are not found in rng1 so #N/A is returned.
On these occasions I would, if possible, like to draw the users attention to
these values. I would like to have a message box listing the values, or if
this is too much, a simple message saying that certain values were not found
(perhaps these values could then be highlighted). I would prefer to do this
with conditional formatting.

Thanks in advance.

Gareth

Vlookup Error handling
 

On Error Resume Next
For Each cell In rng
cell.Offset(0, 2).Value = Application.VLookup(cell.Value, rng1.Resize(,
10),
7, 0)
If err.number <0 then
cell.Offset(0, 2).Value ="# " & Cell.Value
End If
cell.Offset(0, 3).Value = Application.VLookup(cell.Value, rng1.Resize(,
10),
8, 0)

ditto the If / End if

cell.Offset(0, 4).Value = Application.VLookup(cell.Value, rng1.Resize(,
10),
9, 0)

ditto

Next cell


In the above the offset cell value is the look up info preceded by a "#"
ypu can set te sheet's conditional formatting to test if a cell's value
starts with "#" to say color the cell red.

HTH


--
Patrick Molloy
Microsoft Excel MVP
----------------------------------
"Gareth" wrote in message
...
I am using the following to get information from Sheet1 to Sheet2.

Sub Getdata()
msg = "Do you want to filter the data now?"
Style = vbYesNo + vbDefaultButton1 + vbQuestion
Title = "Filter"
response = MsgBox(msg, Style, Title)
If response = vbYes Then
Application.ScreenUpdating = False
With Worksheets("Sheet2")
Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
With Worksheets("Sheet1")
Set rng1 = .Range(.Cells(2, 3), .Cells(Rows.Count, 3).End(xlUp))
End With
For Each cell In rng
cell.Offset(0, 2).Value = Application.VLookup(cell.Value, rng1.Resize(,
10),
7, 0)
cell.Offset(0, 3).Value = Application.VLookup(cell.Value, rng1.Resize(,
10),
8, 0)
cell.Offset(0, 4).Value = Application.VLookup(cell.Value, rng1.Resize(,
10),
9, 0)
Next cell
Application.ScreenUpdating = True
End If
End Sub

On some occasions values in rng are not found in rng1 so #N/A is returned.
On these occasions I would, if possible, like to draw the users attention
to
these values. I would like to have a message box listing the values, or
if
this is too much, a simple message saying that certain values were not
found
(perhaps these values could then be highlighted). I would prefer to do
this
with conditional formatting.

Thanks in advance.

Gareth