Thread: Use Lookups to change a value in the lookup table

Hey all,

I have a table which contains information about stock, item description, price, barcode, stock qty etc.

I have used lookups to make an invoice so I can sell the product and print a receipt etc. I want to be able to take away the amount sold off the specific product that was found using the barcode and a lookup and take that amount from the stock qty column in the lookup table.

Is there a way in which this can be done? If so how?

I have included an example of the invoice and the lookup table that I want the stock to be taken from.

I have little knowledge in excel and any help would be greatly appreciated.

Thanks

Hi

In order to do what you are looking for you really need to use VBA so that you can carry out the appropriate actions, for example limiting sales to in stock items.

Unfortunately it is not really something that you can achieve using formulas only.

If you would like some examples please let me know and I will upload an example sheet with some code for you to look at.

Regards

Jeff

Hey

Yer I thought it would have to be done using VB.

That would be great if you could put up any examples with the code, it would help alot.

i have a very small understanding of VB so hopefuly with your code it will be enough.

thanks again

Tom

HI

Just returned from a trip and saw your post, should have something for you tomorrow.

Regards

Jeff

Hey

that's great! Thanks a lot for your help

Tom

HI

I have added a couple of macros for you to look at and removed the spin buttons. The buttons I removed as you need to limit the purchase quantity to no more than the in stock item and this has been done with data validation, which prevents incorrect entries.

There are two buttons, one to add additional items which includes error trapping and one to check out, also with error trapping, but without print routines and data save, although the area to print will be selected.

This is really just to give you an idea of what is involved and to see what questions you may have once you have a chance to go through the code.

Regards

Jeff

Hey,

Thanks alot this is really help full.

couple of questions on the code which i dont understand is what the lrow variable is and also the line

Let lrow = Sheets("Stock Table").Range("f65536").end(xlup).row - 2

not sure what that means and where the lrow variable is and works and also where the range comes from.

and also what the number is in these bits of code

Let Qty = cells(irow, 5).value what the 5 is in this??

also would there be a way poss using a loop so that u didnt have to press the button every time u add an item??

i thought maybe like this

For cell range A6 to A10

Then do all the adding and removing of the stock. is that possible???

thanks again for all your help it has been great.

Tom

Hi

Okay then, first:

lrow is just shorthand for lastrow and the code checks the range from the bottom cell in column f and sets the variable value to that row number, then -2 to give the actual row number that is needed.

The number 5 is the column number as using this notation the first number is the row and the second is the column.

Regarding pressing the button, I had assumed that you would want to expand the number of items and this would facilitate that much more easily, in reality I would place the stock on a separate page for security.

Also you need a record of each transaction also on a separate page, ideally with date and timestamp.

Regards

Jeff

Hey Jeff

Nearly There Now!!!!!

Thanks a lot I understand now lol

all your help has been great and much appreciated.

i have added the code to my actual spreadsheet and have just had one slight problem with it. i have the Lookup table on another sheet and i cant it to take the value of that sheet it takes it off the open sheet. this is the rought out line of my code if you could have a look at it that would be great

Sub REMOVE_STOCK()

Dim LROW As Double, QTY As Integer, BARCODE As Integer

Application.ScreenUpdating = False

Let LROW = Sheets("SALE").Range("A65536").End(xlUp).Row

Let BARCODE = Cells(LROW, 1).Value
Let QTY = Cells(LROW, 6).Value

With Worksheets("STOCK").Range("A3:A35")
Set C = .Find(BARCODE, LookIn:=xlValues, Lookat:=xlWhole)
Let Cells(C.Row, 8).Value = Cells(C.Row, 8).Value - QTY

End With

Application.ScreenUpdating = True

End Sub


I have also attached my sheet.

thanks again for all your help.

Tom

Hi

The problem is in the FIND routine, when you calling the routine from another sheet any cell references without a sheetname refer to the active sheet. If you change your code as follows then it will work okay.

Code:

With Worksheets("STOCK").Range("A3:A35")
 Set C = .Find(BARCODE, LookIn:=xlValues, Lookat:=xlWhole)
     Let Sheets("STOCK").Cells(C.Row, 8).Value = Sheets("STOCK").Cells(C.Row, 8).Value - QTY
End With

regards

Jeff

Yep that Works Great.

Thanks alot for your help again lol.

I have got it all working correctly now, all thanks to you lol.

all your help has been much appreciated.

Would it be ok to PM you if i need any more help further down the line.

thanks again

Tom