How would one go about updating inventory listings based on

[QMega]

I've been beating this horse for a few days now. I'm ready to relent and ask for directions!

I have a set up for inventory updates. The first worksheet in this workbook contains the old inventory data (for all products). The second sheet contains new inventory data (for selected products - much shorter list). Each item is qualified with a Unique ID.
I need a way to look up the appropriate Unique ID, make the updates (to price and quantity). Then dump this in a new worksheet.
I am no good with Excel, so this has been trouble for me.

Any help is very much appreciated!

 

[SkipVought]

Which sheet is THE INVENTORY TABLE?

Is your inventory table a transaction table, ie puts and takes for an item (multiple rows) that net out to on hand, or is it one row per item that reflects the on hand?

Skip,

_[red][/red]

 

[QMega]

It's a one-row-per item deal - the important variables being the UniqueID, price, and quantity. The second sheet is the one we're updating from (i.e. will contain information about the new quantity of product in the garage and how much the manufacturer is charging today). The first sheet is a breakdown of what we have left of the last shipment.

I have to thank you, Skip, for all the good advice you've given others on the boards. It's helped me get a leg up on a number of other projects.

-Meg

 

[SkipVought]

How automated do you want this to be? The more automated, the more VBA coding and maintenance. Are you adequate in VBA to take some tips and apply them to a full-blown system? Seems like you are designing this from scratch.

We could do some simple things like record some macros to insert spreadsheet formulas and copy 'n' paste then customize, or as advanced as ActiveX Data Objects to do database updates -- ALOT more "from scratch" coding.

But you will have to make the basic design, record the macros or write the basic code and get tips along the way.

Skip,

_[red][/red]

 

[QMega]

I don't expect other people to do the work that I'm (theoretically) being paid for. I'm looking for the right footing on this, as I am relatively inexperienced.
The only real automation I'm looking for here is the actual updating bit. I had considered using a multi dimensional array, but I'm not sure how that would work.

I have written enough that the program checks that the right workbook is opened and that there is data in the appropriate fields, then the data is copied and protected as backup in a separate workbook. The data in the working copies are then sorted according to the UniqueID field.

What I need is a decent direction to go in for the program to iterate through the data in the second worksheet, check for that particular UniqueID in the first, update quantity and price if necessary, and the copy that data into the appropriate fields in the third worksheet. Since the data is sorted already, I thought perhaps the array bit would work. But I have not been able to wrap my head around how that would work, really. I do not grok.

 

[SkipVought]

One way would be to do a LOOKUP in a new temporary column in your inv table. This would lookup the item in that row and return the new Qty or Price. If there is no item in the lookup table then use the current Qty/Price -- something like this...
[tt]
if(isna(match(A2,Sheet2!$A$2:$A$100,0)),B2,vlookup(A2,Sheet2!$A$2:$A$100,2,false))
[/tt]
assuming that the columns are item,qty, price in both tables.

So you'd insert that formula via VBA, copy it down thru the rows of inventory data, copy the column and paste special - VALUES in the appropriate Qty/Price column, and then delete the temp column.

Most of it can be recorded. Post back with your recorded code to ge help with customizing it.

Skip,

_[red][/red]

 

[QMega]

I got a #REF error. But changed it to if(isna(match(A2,Sheet1!$A$2:$A$100,0)),B2,vlookup(A2,Sheet2!$A$2:$A$100,2,false))
Then all I got was the return of the B2 cell of Sheet2 for every row that the formula was placed in. How does the syntax work here? I'm confused...

Theoretically, then, I'll run a second loop for the price data. Then I can perform the calculations based on the temporary columns and dump that to the third sheet.

How will this work with 500+ data items?

Thank you again for your help...I hate being the Newb.

 

[SkipVought]

"Then all I got was the return of the B2 cell of Sheet2 for every row that the formula was placed in."

Not if your have your Calculation set to AUTOMATIC. Row 3 will have A3 & B3 etc. Check Tools/Options/Calculation Tab.

Skip,

_[red][/red]

 

[QMega]

It is on auto.

 

[SkipVought]

Then you do not have a valid formula.

Are you familiar with copying excel formulas?

Skip,

_[red][/red]

 

[QMega]

...apparently not.
My experience with Excel to this point has been academic.

 

[SkipVought]

You need to understand basic Excel functionality before you try doing fancy stuff in VBA.


Skip,

_[red][/red]

 

[SkipVought]

...and understanding the Excel Object Model would not hurt.

Skip,

_[red][/red]

 

[WalkerEvans]

OMega,

My guess is that you copied the formula into each line separately, leaving each line with an exact copy of the original (ie, each line contained the reference to B2).

Try inserting the formual into the first line, and then copying down thru each line; this will automatically change all of the references in each line. Put the formula into the row, then hit Control-C to begin the copy process; the original cell will have a flashing border. Then cursor down one row, hold down the Shift key, and cursor down thru the rows until you reach the last row that needs the formula. Let go of the Shift key and hit Control-V.

Your rows will all be updated with the formula, and all of them will have properly cell references.

Hope this helps.

 

[QMega]

Thank you so much for you help. After some thought, I decided to go a different route, code snippet goes thusly:

     With rnVT
        For Each rnCheckVal In rnVT
           For Each rncell In rnALR
                 On Error Resume Next
                    If rncell = rnCheckVal.Value Then
                        Set rncell = rncell.Resize(1, 4)
                        rncell.Copy
                        rnPasted.PasteSpecial (xlPasteValuesAndNumberFormats)
                       With wsCopies
                       Set rngPasted = .Range("E65536").End(xlUp).Offset(1, 0)
                    End With
             
        Next rncell
        Next rnCheckVal
    End With

It then goes on to perform other nonsense and whatnot. Works better this way for the design I decided on.