Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA VLOOKUP AND No Value Match
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA VLOOKUP AND No Value Match
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!) -- Regards; Rob ------------------------------------------------------------------------ "JimFor" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA VLOOKUP AND No Value Match
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 wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA VLOOKUP AND No Value Match
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA VLOOKUP AND No Value Match
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA VLOOKUP AND No Value Match
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.) JimFor wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup retunrning a match, when not a match... | Excel Worksheet Functions | |||
vlookup retunrning a match, when not a match... | Excel Worksheet Functions | |||
vlookup retunrning a match, when not a match... | Excel Worksheet Functions | |||
second or third match in vlookup() or Match() | Excel Worksheet Functions | |||
index Match, or Vlookup Match.. | Excel Worksheet Functions |