Only allow specified strings in a range

[Slick Willie]

How could write a formula to only allow certain words or phrases in a
cell. I'm using the VLOOKUP function, and if a user enters a value
that is not in my table, the function returns #N/A. When I try to
test a cell with #N/A, I get a debug in VB, or another #N/A with Excel
formulas.

 

[Harald Staff]

Hi

Use data validation for this. See Debra's page
http://www.contextures.com/xlDataVal02.html

HTH. best wishes Harald

 

[Tom Ogilvy]

Also, to Check for an error

set cell = Range("B9")
' checks for any error
if iserror(Cell.Value) then

or
' checks specifically for #N/A
if Cell.Text = "#N/A" then

or
' checks specifically for #N/A
if cell.Value = cvErr(xlErrNA) then