VBA Excel Macro to subtract cell from different sheet using VLOOKUP

0
votes

I'm try to write a macro that will auto update stock by taking the ammount used from one cell, subtracting it from a total in a different sheet.

Sheet view:

enter image description here

So in this instance - I want to subtract "kgs" which is cell range 'I17:I24' from what is "kgs available" which is held in the sheet "Malt" and updated from a VLOOKUP defined from the "Batch Code" in the Brew Sheet.

I'm guessing the code would probably have to VLOOKUP that batch code in the Malt sheet and subtract the "kgs" in the "Brew Sheet" sheet from the "kgs available" in the "Malt" sheet (Column L)

What also might contribute complications is if the cells in range E17:24, which contain the Batch Codes for the VLOOKUP have no values. Would that throw up errors, if though I may not need to use all of the cells in that range? (If that makes sense - sometimes, I may only need to use 1/2 of the 8 cells in the range)

Apologises in advance if my question is vague. My coding knowledge is very limited.

Thanks for you help.

1
vbaexcel

1 Answers

0
votes

you need to get the sum of KGs in range E17:24 where the batch code matched the batch code on the malt sheet

=sumif(Malt!$E$17:$E$24,L17,Malt!$I$17:$I$24)

Then all you need to do is subtract that from the available value in column I

=I17-sumif(Malt!$E$17:$E$24,L17,Malt!$I$17:$I$24)

Not sure if I have the columns correct.