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

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