VBA Vlookup with cumulative results

Excel_Blonde

New Member
Joined
Aug 8, 2018
Messages
44
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)
ColumnPartQty
AApple12
BBanana6
CCarrot18
DCake0
EBiscuit7

<tbody>
</tbody>

Active Sheet (Sheet2)
ColumnOrderPartQtyShortages
A1Banana60
B2Apple30
C3Apple60
D4Banana33
E5Apple52
F6Cake22
G7Apple88

<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.
 
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?
 
Upvote 0

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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:
Code:
[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]
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top