VBA Vlookup with cumulative results

Excel_Blonde · Feb 22, 2019

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 · Feb 25, 2019

Akuini said:
Sorry, forget to declare the variables.
Just add this in the beginning of the code:

Code:

Dim i As Long Dim va Dim d As Object

Thank you,

Next one is "va(i, 3) = va(i, 2) - d(va(i, 1))" = type mismatch.

I really appreciate your help with this. I'm trying to read it to figure out where its going wrong I just don't yet understand enough to figure out for myself.

Akuini · Feb 25, 2019

Excel_Blonde said:
Thank you,

Next one is "va(i, 3) = va(i, 2) - d(va(i, 1))" = type mismatch.

I really appreciate your help with this. I'm trying to read it to figure out where its going wrong I just don't yet understand enough to figure out for myself.

Are you saying the code didn’t work using your example in post 1 or on your actual data?

Maybe in your actual data there is data in col Qty (in both sheet) that isn’t a number?

Excel_Blonde · Feb 25, 2019

Excel_Blonde said:
Thank you,

Next one is "va(i, 3) = va(i, 2) - d(va(i, 1))" = type mismatch.

I really appreciate your help with this. I'm trying to read it to figure out where its going wrong I just don't yet understand enough to figure out for myself.

I've just realised this is likely cause by the actual data range I'm looking at.

Columns A:W with the Order number in A, the part number in K, the qty required in O and the shortages column in W.

There are only numbers in the qty column but there are some 0 qtys.

The = va(i, 2) appears to be referring to the next column over. Can you explain the for statement please? I may be able to figure out whats going wrong if i know whats its doing.

For i = 1 To UBound(va, 1)
If d.Exists(va(i, 1)) Then
va(i, 3) = va(i, 2) - d(va(i, 1))
d(va(i, 1)) = d(va(i, 1)) - va(i, 2)
' If d(va(i, 1)) < 0 Then d(va(i, 1)) = 0
If va(i, 3) < 0 Then va(i, 3) = 0

Akuini · Feb 25, 2019

This line is where the code populate the data to the array:

Code:

va = Range("C2:E" & Cells(Rows.count, "C").End(xlUp).Row)

so it tells the code to populate data from col C-E to va.
If the actual data are in separate columns then we need to populate the data in each column to different array.

So in sheet2:
Order number in A, the part number in K, the qty required in O and the shortages column in W.

What is the layout of the data in sheet3 like?

Excel_Blonde · Feb 25, 2019

Akuini said:
This line is where the code populate the data to the array:

Code:

va = Range("C2:E" & Cells(Rows.count, "C").End(xlUp).Row)

so it tells the code to populate data from col C-E to va.
If the actual data are in separate columns then we need to populate the data in each column to different array.

So in sheet2:
Order number in A, the part number in K, the qty required in O and the shortages column in W.

What is the layout of the data in sheet3 like?

Sheet 3 has the part code in B and the on hand stock qty in M.

Akuini · Feb 25, 2019

Ok, try this:

Code:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1088487b()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1088487-vba-vlookup-cumulative-results.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] va, vb, vw
[COLOR=Royalblue]Dim[/COLOR] d [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Object[/COLOR]


[COLOR=Royalblue]Set[/COLOR] d = CreateObject([COLOR=brown]"scripting.dictionary"[/COLOR])
d.CompareMode = vbTextCompare
[COLOR=Royalblue]With[/COLOR] Sheets([COLOR=brown]"Sheet3"[/COLOR])
vb = .Range([COLOR=brown]"B2:M"[/COLOR] & .Cells(.Rows.count, [COLOR=brown]"B"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(vb, [COLOR=crimson]1[/COLOR])
d(vb(i, [COLOR=crimson]1[/COLOR])) = vb(i, [COLOR=crimson]12[/COLOR])
[COLOR=Royalblue]Next[/COLOR]

n = Range([COLOR=brown]"K"[/COLOR] & Rows.count).[COLOR=Royalblue]End[/COLOR](xlUp).Row
va = Range([COLOR=brown]"K2:O"[/COLOR] & n)
vw = Range([COLOR=brown]"W2:W"[/COLOR] & n)

[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
[COLOR=Royalblue]If[/COLOR] d.Exists(va(i, [COLOR=crimson]1[/COLOR])) [COLOR=Royalblue]Then[/COLOR]
    vw(i, [COLOR=crimson]1[/COLOR]) = va(i, [COLOR=crimson]5[/COLOR]) - d(va(i, [COLOR=crimson]1[/COLOR]))
    d(va(i, [COLOR=crimson]1[/COLOR])) = d(va(i, [COLOR=crimson]1[/COLOR])) - va(i, [COLOR=crimson]5[/COLOR])
[I][COLOR=seagreen]'    If d(va(i, 1)) < 0 Then d(va(i, 1)) = 0[/COLOR][/I]
    [COLOR=Royalblue]If[/COLOR] vw(i, [COLOR=crimson]1[/COLOR]) < [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR] vw(i, [COLOR=crimson]1[/COLOR]) = [COLOR=crimson]0[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]Next[/COLOR]

Range([COLOR=brown]"W2"[/COLOR]).Resize(UBound(vw, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR]) = vw
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

Excel_Blonde · Feb 25, 2019

Akuini said:

Ok, try this:

Code:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1088487b()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1088487-vba-vlookup-cumulative-results.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] va, vb, vw
[COLOR=Royalblue]Dim[/COLOR] d [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Object[/COLOR]


[COLOR=Royalblue]Set[/COLOR] d = CreateObject([COLOR=brown]"scripting.dictionary"[/COLOR])
d.CompareMode = vbTextCompare
[COLOR=Royalblue]With[/COLOR] Sheets([COLOR=brown]"Sheet3"[/COLOR])
vb = .Range([COLOR=brown]"B2:M"[/COLOR] & .Cells(.Rows.count, [COLOR=brown]"B"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(vb, [COLOR=crimson]1[/COLOR])
d(vb(i, [COLOR=crimson]1[/COLOR])) = vb(i, [COLOR=crimson]12[/COLOR])
[COLOR=Royalblue]Next[/COLOR]

n = Range([COLOR=brown]"K"[/COLOR] & Rows.count).[COLOR=Royalblue]End[/COLOR](xlUp).Row
va = Range([COLOR=brown]"K2:O"[/COLOR] & n)
vw = Range([COLOR=brown]"W2:W"[/COLOR] & n)

[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
[COLOR=Royalblue]If[/COLOR] d.Exists(va(i, [COLOR=crimson]1[/COLOR])) [COLOR=Royalblue]Then[/COLOR]
    vw(i, [COLOR=crimson]1[/COLOR]) = va(i, [COLOR=crimson]5[/COLOR]) - d(va(i, [COLOR=crimson]1[/COLOR]))
    d(va(i, [COLOR=crimson]1[/COLOR])) = d(va(i, [COLOR=crimson]1[/COLOR])) - va(i, [COLOR=crimson]5[/COLOR])
[I][COLOR=seagreen]'    If d(va(i, 1)) < 0 Then d(va(i, 1)) = 0[/COLOR][/I]
    [COLOR=Royalblue]If[/COLOR] vw(i, [COLOR=crimson]1[/COLOR]) < [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR] vw(i, [COLOR=crimson]1[/COLOR]) = [COLOR=crimson]0[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]Next[/COLOR]

Range([COLOR=brown]"W2"[/COLOR]).Resize(UBound(vw, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR]) = vw
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

Hi Akuini,

Thank you very much for the time you are putting into this. It runs without any errors but doesn't seem to do anything. It returns the shortages by line but isn't accumulating down the list.

Akuini · Feb 25, 2019

Here's the working example:

https://www.dropbox.com/s/9i5234513mozmam/a1088487-vba-vlookup-cumulative-results.xlsm?dl=0

Akuini · Feb 25, 2019

The result:

Excel 2013 32 bit
[Table="width:, class:head"][tr=bgcolor:#008B8B][th] [/th][th]

K

[/th][th]

L

[/th][th]

M

[/th][th]

N

[/th][th]

O

[/th][th]

P

[/th][th]

Q

[/th][th]

R

[/th][th]

S

[/th][th]

T

[/th][th]

U

[/th][th]

V

[/th][th]

W

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

1

[/td][td]Part[/td][td][/td][td][/td][td][/td][td]Qty[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Shortages[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

2

[/td][td]Banana[/td][td][/td][td][/td][td][/td][td]

6