# VBA Vlookup with cumulative results

#### Excel_Blonde

##### New Member
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)
 Column Part Qty A Apple 12 B Banana 6 C Carrot 18 D Cake 0 E Biscuit 7

<tbody>
</tbody>

Active Sheet (Sheet2)
 Column Order Part Qty Shortages A 1 Banana 6 0 B 2 Apple 3 0 C 3 Apple 6 0 D 4 Banana 3 3 E 5 Apple 5 2 F 6 Cake 2 2 G 7 Apple 8 8

<tbody>
</tbody>

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 Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Does this do it?

=MIN(D2,ABS(MIN(0,VLOOKUP(C2,Sheet3!B:C,2,0)-SUMIFS(\$D\$1:D1,\$C\$1:C1,C2)-D2)))

Does this do it?

=MIN(D2,ABS(MIN(0,VLOOKUP(C2,Sheet3!B:C,2,0)-SUMIFS(\$D\$1:D1,\$C\$1:C1,C2)-D2)))

Thanks Steve, that does seem to work but now I need to try to put it into VBA. This does give me a starting point though.

Thank you very much

Thanks Steve, that does seem to work but now I need to try to put it into VBA. This does give me a starting point though.

Thank you very much

Ok, I'm struggling with this. Can anyone help me to use this within VBA? I've tried manipulating it to get it to work but I'm just not clever enough .
Any help would be appreciated.

Ok, I'm struggling with this. Can anyone help me to use this within VBA? I've tried manipulating it to get it to work but I'm just not clever enough .
Any help would be appreciated.

Hi All,

I made it work with the below solution:

"=MIN(RC[-8],ABS(MIN(0,VLOOKUP(RC[-12],Sheet3!R1:R1048576,13,0)-SUMIFS(R1C15:R[-1]C[-8],R1C11:R[-1]C[-12],RC[-12])-RC[-8])))"

This only shows shortages by line. What if I wanted them to accumulate (add up going down the list)? I will continue to work at it, but if anyone can offer any suggestions it would help massively.

Hi, Excel_Blonde
I don’t quite understand what you mean by “add up going down the list”.
In order 5 Apple has shortage of 2, then in order 7 Apple has additional Qty of 8, so why does the shortage is 8? Shouldn’t it be 2 + 8 = 10?

Hi, Excel_Blonde
I don’t quite understand what you mean by “add up going down the list”.
In order 5 Apple has shortage of 2, then in order 7 Apple has additional Qty of 8, so why does the shortage is 8? Shouldn’t it be 2 + 8 = 10?

That's how I would like it to work, that's what I meant by "add up going down the list", a cumulative figure. Any help would be appreciated.

Your answer isn't clear to me, so it should be 10, correct?
Maybe this:

Code:
``````[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1088487a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1088487-vba-vlookup-cumulative-results.html[/COLOR][/I]

[COLOR=Royalblue]Set[/COLOR] d = CreateObject([COLOR=brown]"scripting.dictionary"[/COLOR])
d.CompareMode = vbTextCompare
[COLOR=Royalblue]With[/COLOR] Sheets([COLOR=brown]"Sheet3"[/COLOR])
va = .Range([COLOR=brown]"B2:C"[/COLOR] & .Cells(.Rows.count, [COLOR=brown]"C"[/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(va, [COLOR=crimson]1[/COLOR])
d(va(i, [COLOR=crimson]1[/COLOR])) = va(i, [COLOR=crimson]2[/COLOR])
[COLOR=Royalblue]Next[/COLOR]

va = Range([COLOR=brown]"C2:E"[/COLOR] & Cells(Rows.count, [COLOR=brown]"C"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)

[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]
va(i, [COLOR=crimson]3[/COLOR]) = va(i, [COLOR=crimson]2[/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]2[/COLOR])
[I][COLOR=seagreen]'    If d(va(i, 1)) < 0 Then d(va(i, 1)) = 0[/COLOR][/I]
[COLOR=Royalblue]If[/COLOR] va(i, [COLOR=crimson]3[/COLOR]) < [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR] va(i, [COLOR=crimson]3[/COLOR]) = [COLOR=crimson]0[/COLOR]

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

Range([COLOR=brown]"C2"[/COLOR]).Resize(UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]3[/COLOR]) = va

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

Your answer isn't clear to me, so it should be 10, correct?
Maybe this:

Code:
``````[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1088487a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1088487-vba-vlookup-cumulative-results.html[/COLOR][/I]

[COLOR=Royalblue]Set[/COLOR] d = CreateObject([COLOR=brown]"scripting.dictionary"[/COLOR])
d.CompareMode = vbTextCompare
[COLOR=Royalblue]With[/COLOR] Sheets([COLOR=brown]"Sheet3"[/COLOR])
va = .Range([COLOR=brown]"B2:C"[/COLOR] & .Cells(.Rows.count, [COLOR=brown]"C"[/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(va, [COLOR=crimson]1[/COLOR])
d(va(i, [COLOR=crimson]1[/COLOR])) = va(i, [COLOR=crimson]2[/COLOR])
[COLOR=Royalblue]Next[/COLOR]

va = Range([COLOR=brown]"C2:E"[/COLOR] & Cells(Rows.count, [COLOR=brown]"C"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)

[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]
va(i, [COLOR=crimson]3[/COLOR]) = va(i, [COLOR=crimson]2[/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]2[/COLOR])
[I][COLOR=seagreen]'    If d(va(i, 1)) < 0 Then d(va(i, 1)) = 0[/COLOR][/I]
[COLOR=Royalblue]If[/COLOR] va(i, [COLOR=crimson]3[/COLOR]) < [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR] va(i, [COLOR=crimson]3[/COLOR]) = [COLOR=crimson]0[/COLOR]

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

Range([COLOR=brown]"C2"[/COLOR]).Resize(UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]3[/COLOR]) = va

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

Hi, Thanks for your response. The is returning Compile error. Variable not defined. Referring to 'Set d =', I've had a quick look but cant see whats wrong with it?

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``````

Replies
6
Views
408
Replies
7
Views
285
Replies
4
Views
248
Replies
9
Views
149
Replies
11
Views
574

1,218,899
Messages
6,145,091
Members
450,590
Latest member
Naneng

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back