How do I get a popup window on a VLOOKUP lookup failure?

M

Mark F

I am using the VLOOKUP function and want to get an error indication
if there is not an exact match for the first argument (lookup_value).

I can get an error indication each cell with a problem by using the
4th argument to VLOOKUP and using the value "FALSE".

How do I get a indication that there a problem in either a fixed
location in the spreadsheet or a popop window?

I am using Office 2003, but, if need be, I can move to Office 2010.
I am using Windows XP and cannot upgrade.
 
M

Mark F

Assumes your lookup formula is in cell H4...
Enter this formula in your "fixed location" cell: =IF(ISERROR(H4),"PROBLEM","")
'---
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(Special Print XL add-in: long columns printed side by side)
Doesn't do what I want: I want ANY error to show up in some fixed
location.

There will be 1000s of cells.
 
G

Gord Dibben

You want a message box pop up if there is any error in any cell out of the
1000's of cells?

What if there are 1,329 errors?

Do you want a message pop up for each one or.........???


Gord Dibben MS Excel MVP
 
M

Mark F

You want a message box pop up if there is any error in any cell out of the
1000's of cells?

What if there are 1,329 errors?

Do you want a message pop up for each one or.........???
One time, but a count of the found errors would be better, so I
know have an idea of how many problems I fixed when I fixed one
cell.
 
G

Gord Dibben

One time, but a count of the found errors would be better, so I
know have an idea of how many problems I fixed when I fixed one
cell.

This may get you started but not sure what you mean about fixing one cell.

Sheet event code to count error cells after calculation takes place.

Private Sub Worksheet_Calculate()
Dim rng As Range
On Error GoTo endit
Application.EnableEvents = False
Set rng = Me.Range("A1:M100").SpecialCells(xlCellTypeFormulas, 16)
MsgBox rng.Count & " Errors found!!"
endit:
Application.EnableEvents = True
End Sub


Gord
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top