VBA VLOOKUP AND No Value Match

[JimFor]

Hi,

I'm working on a program which uses the VLOOKUP function. I have some account
numbers on one sheet (Sheet2) and want to see if there are correspoding
inventory amounts on another sheet(Sheet1). Things work OK if there are
inventory amounts. These numbers are placed next to the proper account number
on Sheet2. However, in some cases the an account number found on Sheet2 is not
found on Sheet1. . In that case, I want to leave the cell on Sheet2 next to
an account number which does appear on Sheet1 blank. Nothing will appear in
it. So far, I have been unable to do this. Here is a bit of my program.

Sub ZOO()

Cells(5, 8) = WorksheetFunction.VLookup(ActiveSheet.Range("A2"), _
Worksheets("Sheet1").Range("A2:C4"), 2, 0)
End Sub

From what I have read, I have to replace the last zero with the word "FALSE"
and use an IF(ISNA...code to do what I want. I first tried the putting a
"FALSE" in the program, which, I believe, should have placed "N/A" next to any
account which did not have an inventory amount on sheet 1. That does not work.
When I put in an account number in Sheet2 which is not on Sheet1 and run the
program, I do not get a "FALSE" on Sheet 2 but an error message "400." I
stopped there and have not attempted to include the "IF(ISNA..." code in the
VLOOKUP function. Can anyone tell me what I have done wrong and how I can get
the VLOOKUP to put a blank in a cell in Sheet2 next to an account number if the
program cannot find that account number on Sheet 1?

Thanks

 

[RWN]

When I do this I use;

=if(ISNA(VLOOKUP(key,range,element,FALSE))," ",VLOOKUP(key,range,element,FALSE))
Says - "if the key isn't found put a blank in the cell, ELSE put the found value in the
cell"

Always thought there should be an easier way (rather than having to copy the VLOOKUP in
the ELSE condition).
(there probably is and I'm sure someone will point it out!)

 

[Dave Peterson]

Worksheet.function.vlookup()
Causes a runtime error (1004) for me when there is no match.

You can either check for that error:

Option Explicit
Sub ZOO1()

Dim res As Variant

With ActiveSheet
On Error Resume Next
res = WorksheetFunction.VLookup(.Range("A2").Value, _
Worksheets("Sheet1").Range("A2:C4"), 2, 0)
If Err.Number <> 0 Then
res = CVErr(xlErrNA)
Err.Clear
End If
On Error GoTo 0
.Cells(5, 8).Value = res
End With

End Sub

Or you can use Application.Vlookup(). This returns an error value that you can
check:

Option Explicit
Sub ZOO2()

Dim res As Variant

With ActiveSheet
res = Application.VLookup(.Range("A2").Value, _
Worksheets("Sheet1").Range("A2:C4"), 2, 0)

If IsError(res) Then
.Cells(5, 8).Value = CVErr(xlErrNA)
Else
.Cells(5, 8).Value = res
End If
End With

End Sub

 

[JimFor]

Thanks for the help but the program still does not work. If I check for an
error, I get "N/A" in the cell (5,8). Can anyone tell me what that means and
what I should do about it?

As I suspected, using the "IF(ISNA" code as suggested does not work either.
The problem may be with my original program.
Here it is again:

Sub ZOO()
Cells(5, 8) = WorksheetFunction.VLookup(ActiveSheet.Range("A2"), _
Worksheets("Sheet1").Range("A2:C4"), 2, 0)
End Sub

(Here is another version)
Sub ZOO()
Cells(5, 8) = WorksheetFunction.VLookup(Sheet2.Cells(2, 1), _
Worksheets("Sheet1").Range("A2:B4"), 2, 0)
End Sub


From what I can tell, my original program only works if there is are identical
account numbers in both sheets. However, since the program does have an ending
"Zero" in it, the program should put "N/A" if it does not find an account
number on sheet1 if the same account number appears on sheet2. It does not.
When I run it, it gives me a 400 error. Once this part of the program works, I
think adding the "IF(ISNA" code should also work. Can anyone tell me why the
above program does not return an "N/A" value when the fact that it ends with
....Range("A2:C4"), 2, 0) indicates that it should?

Thanks

 

[JimFor]

I finally was able to get "N/A" placed in a cell when an account number which
appeared on sheet 2 did not appear on sheet 1. Read in a book that at times
"WorksheetFunction.VLookup" does not work correctly and if that happens one
should try using "Application.VLookup." When I used that code, the program
worked. However, I still cannot get the program to insert a blank instead of
"N/A". I am getting another error message and have posed my question under the
"VLOOKUP Compile/Syntax Error" thread.

Thanks for your help.

 

[Dave Peterson]

Taken from my first post and modified:

Option Explicit
Sub ZOO2()

Dim res As Variant

With ActiveSheet
res = Application.VLookup(.Range("A2").Value, _
Worksheets("Sheet1").Range("A2:C4"), 2, 0)

If IsError(res) Then
.Cells(5, 8).Value = "" 'CVErr(xlErrNA)
Else
.Cells(5, 8).Value = res
End If
End With

End Sub

I changed this line:

..Cells(5, 8).Value = CVErr(xlErrNA)
to
..Cells(5, 8).Value = "" 'CVErr(xlErrNA)

(the cverror() stuff is commented out.)