Hi,

I'm working on a program which will calculate an inventory position at any
given date in the current year. I do not know how to stop using the VLOOKUP
function at one point of the program. The inventory data is for various
accounts. There are two groups of inventory data. A total of last year's
inventory totals grouped by Account Number is located on Sheet 1 of a
workbook. The second group of data is segmented by Account number/Order
Date/Purchase Date. It contains all the individual orders and purchaes for all
the accounts for the current year. It is located on Sheet 2.

I will arrive at an inventory total for any particular date by determining
how much inventory an account had during the previous year (Sheet1) and then
subtracting from that amount any orders from the current year and/or adding to
that amount any purchases made in the current year up to the desired date
(Sheet2). I will use the VLOOKUP function to go through a list of last year's
accounts to pull up last year's inventory total for a particular account.
However, I do not know how to code the program if an account has no inventory
at the end of the previous year. The program then has to exit the VLOOKUP part
of the program and get to the first transaction for that account in Sheet 2 and
start subtracting sales and adding purchases. So my question is, "If the
program cannot find any inventory for an account on Sheet 1, how do I code it
to leave the VLOOKUP function and start calculating transactions found on Sheet
2?"

Thanks

Jim,

I know what you are tying to accomplish and I do not think that Vlookup is
the right approach. I assume that all your data has a Part No. of some sort
to identify the SKU.

Use the Match formula to locate the applicable line in your opening
inventory data, then use the Index formula to locate the value (quantity or
cost). Be sure that the last item in the match range has zero values so if
Match can't locate the Part No., your Index formula will return zero.

Next, you need to add up all the purchases or sales in your activity
database by quantity or cost. This can be accomplished as follows:

1. place your "inventory" activity range into an array
2. scan the array in a loop to find each transaction for that Part No.
3. sum the results
4. add or deduct the results from opening inventory to get the balance at a
given date

You can also avoid using VBA entirely and use a pivot table to summarize all
your inventory activity by Part No., then use Match/Index approach to so
find your inventory additions and sales. The pivot table approach is much
faster but not totally automated. Of course you can build pivot table using
VBA; but, that is a bit complicated to go into here.

I hope this helps.

Allan P. London, CPA
150 Spear St St. 1700
San Francisco, Ca.


"JimFor" wrote in message
...
Hi,

I'm working on a program which will calculate an inventory position at any
given date in the current year. I do not know how to stop using the
VLOOKUP
function at one point of the program. The inventory data is for various
accounts. There are two groups of inventory data. A total of last year's
inventory totals grouped by Account Number is located on Sheet 1 of a
workbook. The second group of data is segmented by Account number/Order
Date/Purchase Date. It contains all the individual orders and purchaes
for all
the accounts for the current year. It is located on Sheet 2.

I will arrive at an inventory total for any particular date by
determining
how much inventory an account had during the previous year (Sheet1) and
then
subtracting from that amount any orders from the current year and/or
adding to
that amount any purchases made in the current year up to the desired date
(Sheet2). I will use the VLOOKUP function to go through a list of last
year's
accounts to pull up last year's inventory total for a particular account.
However, I do not know how to code the program if an account has no
inventory
at the end of the previous year. The program then has to exit the VLOOKUP
part
of the program and get to the first transaction for that account in Sheet
2 and
start subtracting sales and adding purchases. So my question is, "If the
program cannot find any inventory for an account on Sheet 1, how do I code
it
to leave the VLOOKUP function and start calculating transactions found on
Sheet
2?"

Thanks

Thanks much for your reply. Each run will be for one product. There is one
product number per each time I use the program. So I don't have to get
involved with SKUs and the like. I'm slowly learning about this. Looks like
the VLOOKUP function can return either a blank or a zero if you add some "IF"
and "ISNA" coding to it. Have not tried it out yet but I will.

Your response sounds interesting. I really have to write a program do all
this. It has to be very simple to use. Right now I'm working on a user input
box which will allow the user to input a beginning date and ending date, then
click an "OK" button and..."Presto!" The transaction data as well as the
beginning and ending inventory amounts for each account will appear. If I
follow your steps:

"1. place your "inventory" activity range into an array
2. scan the array in a loop to find each transaction for that Part No.
3. sum the results
4. add or deduct the results from opening inventory to get the balance at a
given date"


....I also have to keep all the transaction data for the period on the
spreadsheet. That is, I can't just get the totals. For any particular
transaction period, say March 2 through June 5, I have to calculate the beginng
and ending inventory totals as well as keep the individual transaction date of
the transaction, the description of the transaction, the item price etc. All
this has to be by account number. Will your approach allow me to do this?

Thanks...I'm slowly making progress...