Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP and Missing Values
Hi,
Working on a program which has last period inventory totals by account on sheet 1 and current period transactions (sales and purchases) on sheet 2. Sheet 3 will have beginning inventory amounts by accounts (among other things). I use the VLOOKUP in VBA to determine the beginning inventory amounts for each account I have transactions for. Works OK but I do not know what to do if sheet 2 has some accounts which have no corresponding last period inventory totals on sheet 1, i.e., their account numbers do no appear on sheet 1. I get an "N/A" in a cell on sheet 3 in the column which contains other values received from the VLOOKUP and the program stops. . Can anyone tell me how to set up the VLOOKUP to enter a zero in a cell if it does not find an account number on sheet 1? I need to do this because I will have to do some calculations with that number for that account. Here is the line of programming I use:. Worksheets("Sheet3").Cells(Kook, 10) = Application.VLookup(Sheet2.Cells(Count, 3), _ Worksheets("Sheet1").Range("A2:B9"), 2, 0) Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP and Missing Values
Dim res as Variant
res = Application.VLookup(Sheet2.Cells(Count,3), _ Worksheets("Sheet1").Range("A2:B9"), 2, 0) if iserror( Res) then Worksheets("Sheet3").Cells(Kook, 10) = 0 Else Worksheets("Sheet3").Cells(Kook, 10) = res End if -- Regards, Tom Ogilvy "JimFor" wrote in message ... Hi, Working on a program which has last period inventory totals by account on sheet 1 and current period transactions (sales and purchases) on sheet 2. Sheet 3 will have beginning inventory amounts by accounts (among other things). I use the VLOOKUP in VBA to determine the beginning inventory amounts for each account I have transactions for. Works OK but I do not know what to do if sheet 2 has some accounts which have no corresponding last period inventory totals on sheet 1, i.e., their account numbers do no appear on sheet 1. I get an "N/A" in a cell on sheet 3 in the column which contains other values received from the VLOOKUP and the program stops. . Can anyone tell me how to set up the VLOOKUP to enter a zero in a cell if it does not find an account number on sheet 1? I need to do this because I will have to do some calculations with that number for that account. Here is the line of programming I use:. Worksheets("Sheet3").Cells(Kook, 10) = Application.VLookup(Sheet2.Cells(Count, 3), _ Worksheets("Sheet1").Range("A2:B9"), 2, 0) Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup returns n/a for all lines - what am I missing? | Excel Worksheet Functions | |||
vlookup missing link | Excel Discussion (Misc queries) | |||
missing values | Excel Discussion (Misc queries) | |||
Missing values from SAS | Excel Worksheet Functions | |||
Excel Compare values in columns & display missing values in a new | Excel Discussion (Misc queries) |