VBA Vlookup with cumulative results

[Excel_Blonde]

Hi There,

Another day, another challenge!

I'm working on a stock shortages report and cant figure out how to lookup the stock value in another sheet and then cumulative subtract as it moves down the list.

E.g
Stock levels (Sheet3)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column[/TD]
[TD]Part[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Apple[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Banana[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Carrot[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Cake[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]Biscuit[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

Active Sheet (Sheet2)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column[/TD]
[TD]Order[/TD]
[TD]Part[/TD]
[TD]Qty[/TD]
[TD]Shortages[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]Banana[/TD]
[TD]6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD]Apple[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]3[/TD]
[TD]Apple[/TD]
[TD]6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]4[/TD]
[TD]Banana[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]5[/TD]
[TD]Apple[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]6[/TD]
[TD]Cake[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]7[/TD]
[TD]Apple[/TD]
[TD]8[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

I'm not sure whether I need to have a cumulative shortage or shortage by order so both solutions would be helpful.

Any help would be appreciated.

 

[Excel_Blonde]

I have recreated the original example and the script works fine. It obviously doesn't like something about the data I'm trying to work with. Could it be because there are spaces, hyphens, forwards slashes etc within the part data (in column K Sheet1 and B sheet2?

 

[Akuini]

I have recreated the original example and the script works fine. It obviously doesn't like something about the data I'm trying to work with. Could it be because there are spaces, hyphens, forwards slashes etc within the part data (in column K Sheet1 and B sheet2?

Maybe there are some trailing spaces. Try removing those space by running this macro:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] trimSpace()
[COLOR=Royalblue]Dim[/COLOR] rng [COLOR=Royalblue]As[/COLOR] Range, vb
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]With[/COLOR] Sheets([COLOR=brown]"Sheet3"[/COLOR])
[COLOR=Royalblue]Set[/COLOR] rng = .Range([COLOR=brown]"B2:B"[/COLOR] & .Cells(.Rows.Count, [COLOR=brown]"B"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)
    vb = rng.Value
    [COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(vb, [COLOR=crimson]1[/COLOR])
        vb(i, [COLOR=crimson]1[/COLOR]) = Trim(vb(i, [COLOR=crimson]1[/COLOR]))
    [COLOR=Royalblue]Next[/COLOR]
    rng.Value = vb
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]

[COLOR=Royalblue]With[/COLOR] Sheets([COLOR=brown]"Sheet2"[/COLOR])
[COLOR=Royalblue]Set[/COLOR] rng = .Range([COLOR=brown]"K2:K"[/COLOR] & .Cells(.Rows.Count, [COLOR=brown]"K"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)
    vb = rng.Value
    [COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(vb, [COLOR=crimson]1[/COLOR])
        vb(i, [COLOR=crimson]1[/COLOR]) = Trim(vb(i, [COLOR=crimson]1[/COLOR]))
    [COLOR=Royalblue]Next[/COLOR]
    rng.Value = vb
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]