Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Handling | Excel Discussion (Misc queries) | |||
Error handling VLOOKUP. #N/A - can it be 0 to allow sumation. | Excel Worksheet Functions | |||
Handling #NUM! error | Excel Worksheet Functions | |||
Error Handling | Excel Programming | |||
Error Handling | Excel Programming |