avoiding #N/A when using vlookup
 

I'm using the Worksheet_Change event and the vlookup function in VBA to
populate the city and state fields in a worksheet when the user types a
value into the zip code field. This is what I'm using to do it:

With Target
.Offset(-2, 0) = "=VLOOKUP(ContactZip,zipCodeLookup,2,FALSE)"
.Offset(-2, 0).Copy
.Offset(-2, 0).PasteSpecial Paste:=xlPasteValues
.Offset(-1, 0) = "=VLOOKUP(ContactZip,zipCodeLookup,3,FALSE)"
.Offset(-1, 0).Copy
.Offset(-1, 0).PasteSpecial Paste:=xlPasteValues
End With

I'm only storing the zip codes for our state in Range("zipCodeLookup"), and
for the most part I'm avoiding #N/A errors when users enter zip codes from
other states by testing the inequalities

If Target.Address = "$E$19" And _
CVar(Target.Value) = 90000 And _
CVar(Target.Value) <= 96699 Then
. . .

This all works fine as far as it goes. However, if a user enters a zip code
between 90000 and 96699 that's not in the list of zip codes I'm using, they
get the #N/A message. So I need to modify my code so that if they type a
value between 90000 and 96699 that's not in my list, it doesn't produce the
#N/A message, and it runs the Exit Sub command. It's also important that
the procedure doesn't clear whatever values the user might have entered
manually in the City and State fields ( Offset(-1, 0) and Offset(-2, 0) ).

So what I need is an "If" statement that would test to see if the value
entered in the cell exists in the list. If not, I'd like to run the Exit
Sub command. Can anyone tell me what that If condition should be?

Thanks in advance.

Paul

avoiding #N/A when using vlookup
 

I don't know if this is what you're looking for but I initially put the lookup value in a cell that should be to the right of the screen. Then after a value is placed in it, it is tested whether there is an error and if not then copied back to the cells where your code places them. I couldn't see another way of testing prior to placing the City and State in the final cells

If Target.Address = "$E$19" The
Temp = CVar(Target.Value
If Temp = 90000 And
Temp <= 96699 The

Application.ScreenUpdating = Fals
With Targe
.Offset(-2, 50) = "=VLOOKUP(ContactZip,zipCodeLookup,2,FALSE)
.Offset(-2, 50).Cop
.Offset(-2, 50).PasteSpecial Paste:=xlPasteValue
.Offset(-1, 50) = "=VLOOKUP(ContactZip,zipCodeLookup,3,FALSE)
.Offset(-1, 50).Cop
.Offset(-1, 50).PasteSpecial Paste:=xlPasteValue
End Wit

End I

If IsError(Range("BC17")) The
Range("BC17").ClearContent
Range("BC18").ClearContent
Application.ScreenUpdating = Tru
Exit Su
Els
Temp = Range("BC17"
Range("BC17").Cop
Range("E17").PasteSpecia
Range("BC18").Cop
Range("E18").PasteSpecia

Range("BC17").ClearContent
Range("BC18").ClearContent
Application.ScreenUpdating = Tru
End I
End I

avoiding #N/A when using vlookup
 

Paul,
You can use Application.WorkSheetFunction.VLookUp to get the value directly,
without using the worksheet.
Test for "N/A", then decide what to do. If the first errors, the second will
also.

In the line "CVar(Target.Value) = 90000", are you comparing numerically or
alphabetically ?


NickHK

"Paul James" wrote in message
...
I'm using the Worksheet_Change event and the vlookup function in VBA to
populate the city and state fields in a worksheet when the user types a
value into the zip code field. This is what I'm using to do it:

With Target
.Offset(-2, 0) = "=VLOOKUP(ContactZip,zipCodeLookup,2,FALSE)"
.Offset(-2, 0).Copy
.Offset(-2, 0).PasteSpecial Paste:=xlPasteValues
.Offset(-1, 0) = "=VLOOKUP(ContactZip,zipCodeLookup,3,FALSE)"
.Offset(-1, 0).Copy
.Offset(-1, 0).PasteSpecial Paste:=xlPasteValues
End With

I'm only storing the zip codes for our state in Range("zipCodeLookup"),
and
for the most part I'm avoiding #N/A errors when users enter zip codes from
other states by testing the inequalities

If Target.Address = "$E$19" And _
CVar(Target.Value) = 90000 And _
CVar(Target.Value) <= 96699 Then
. . .

This all works fine as far as it goes. However, if a user enters a zip
code
between 90000 and 96699 that's not in the list of zip codes I'm using,
they
get the #N/A message. So I need to modify my code so that if they type a
value between 90000 and 96699 that's not in my list, it doesn't produce
the
#N/A message, and it runs the Exit Sub command. It's also important that
the procedure doesn't clear whatever values the user might have entered
manually in the City and State fields ( Offset(-1, 0) and Offset(-2,
0) ).

So what I need is an "If" statement that would test to see if the value
entered in the cell exists in the list. If not, I'd like to run the Exit
Sub command. Can anyone tell me what that If condition should be?

Thanks in advance.

Paul

avoiding #N/A when using vlookup
 

I tried using the Application.WorksheetFunction.VLookUp but it wasn't supported on the ThisWorkbook module.

avoiding #N/A when using vlookup
 

I don't have an Option Compare statement in the worksheet module, so I must
be using the default of Compare Binary. In any event, the Sub is handling
the comparisons properly. The problem occurs after it gets through the if
statement when the number is within the range tested by the if, but isn't
listed in the Vlookup range.

avoiding #N/A when using vlookup
 

Nice workaround, Rich. I wonder, though, isn't there a way to write some
kind of If statement that checks to see if the number exists in the Vlookup
table?

In other words, is there any way to write an If statement that would test to
see if a value such as 96878 exists in the range "zipCodes"?

Paul

avoiding #N/A when using vlookup
 

That's why I put the value first in a temporary cell. After the VLookup is performed and you either get the #N/A or City and State, then the additional IF statement makes sure the value is not #N/A before copying and pasting. Then I deleted the values from the temporary cells
You are calling the VLookup and putting whatever value it determines in the cell it is needed before testing if the value is correct.

You could also use temporary variables to store the values of City and State cells. If someone entered them first then they won't be blank and you'll have the values available. Continue your code to test the City or State cells if VLookup returned #N/A. Then if they are #N/A you can use the values stored in the temporary variables to paste over the #N/A and replace what someone entered. There is also code you could add that would enter new Zip codes, City and State into your lists. Then the new codes would be available next time someone enters one of them.

avoiding #N/A when using vlookup
 

Thanks, Rich. Your idea of storing the city and state values in a variable
is a very clever way of handling the problem. That sounds like a great way
for me to manage this procedure.

Paul

avoiding #N/A when using vlookup
 

Paul,

Dim RetVal As Variant

RetVal = Application.WorksheetFunction.VLookup(YourZipCode, DataRange,
ColumnNumber, False)

'RetVal's value tells you whether the value was found or not
If IsEmpty(RetVal) = True Then
'Zip Code not found
Else
'Found, so process/display
End If

NickHK

"Paul James" wrote in message
news:Gjo%b.417932$na.807680@attbi_s04...
Nice workaround, Rich. I wonder, though, isn't there a way to write some
kind of If statement that checks to see if the number exists in the
Vlookup
table?

In other words, is there any way to write an If statement that would test
to
see if a value such as 96878 exists in the range "zipCodes"?

Paul