Difficult Lookup!!

H

hrithik

Active Member
Joined
Jul 26, 2010
Messages
336
I'm trying to calculate "Days in Inventory" for a list SKUs

I have the following database for SKU receipts:

<TABLE style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; BORDER-COLLAPSE: collapse; DIRECTION: ltr; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid" border=1 cellSpacing=0 cellPadding=0 valign="top"><TBODY><TR><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.697in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">SKUID
</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.667in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt"> Qty
</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 2.163in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt"> Date Received(MM-DD-YY)
</TD></TR><TR><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.697in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">AA001
</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.667in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt"> 1
</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 2.163in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt"> 09/11/2009
</TD></TR><TR><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.697in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">.
</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.667in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">.
</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 2.163in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">
</TD></TR><TR><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.697in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">.
</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.667in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">.
</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 2.163in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">
</TD></TR><TR><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.697in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">AB001
</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.667in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt"> 1
</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 2.163in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt"> 20/12/2009
</TD></TR><TR><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.697in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">AA003
</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.667in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt"> 1
</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 2.163in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt"> 12/01/2010
</TD></TR><TR><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.697in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">AA001
</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.667in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt"> 15
</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 2.163in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt"> 12/11/2010
</TD></TR><TR><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.697in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">.
</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.667in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">.
</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 2.163in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">
</TD></TR><TR><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.697in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">.
</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.667in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">.
</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 2.163in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">
</TD></TR><TR><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.697in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">.
</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.667in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">.
</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 2.163in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">
</TD></TR><TR><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.697in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">AA004
</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.667in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt"> 1
</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 2.163in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt"> 1/03/2011
</TD></TR><TR><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.697in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">AA001
</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.667in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt"> 20
</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 2.163in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt"> 1/03/2011
</TD></TR></TBODY></TABLE>

QTY=quantity :)

Lets consider the SKUID "AA001" as an example,
If current QTY is 10, then Days in Inventory(DOI) is 116 [TODAY()-1/03/2011]
IF current QTY is 25, then DOI is 139 [TODAY()-12/11/2010]
IF current QTY is 36, then DOI is 595 [TODAY()-09/11/2009]

I have to lookup the current QTY in receipts database (bottomup) and find which date(Date_found) the current QTY was received on (assuming LIFO approach) and find the difference [today()-Date_found]

This is to be done for all the 100,000 SKUs we have

I tried MATCH() and Vlookup() but haven't had any success, experts please help on how to do this.....thanks!

Hope this explanation is clear
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
This uses a helper column (column D) to have a running total for QTY for each SKUID. Then uses that running total to do a bottom up lookup.

The formula in H2 ia an array formula. Enter it using Ctrl+Shift+Enter
Excel Workbook
ABCDEFGHI
1SKUIDQtyDate Received(MM-DD-YY)Running TotalSKUIDCurrent QtyDateDays
2AA00119/11/200936AA0012512/11/2010139
30
40
5AB001112/20/20091
6AA003112/1/20101
7AA0011512/11/201035
8A0
90
100
11AA00411/3/20111
12AA001201/3/201120
Sheet
 
Last edited:
Upvote 0
thanks for the reply @AlphaFrog!

I have 924823 records in the database and the formulas you gave take forever to calculate.

for eg: D2 =SUMIF(A2:$A$924823, A2, B2:$B$924823)
<TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR><TD>D2</TD><TD>=SUMIF(A2:$A$65536, A2, B2:$B$65536)</TD></TR></TBODY></TABLE>


Is there any other method, that would b faster?
 
Upvote 0
So you have ~100K unique SKUs and some 924823 records.

The INDEX-MATCH(IF) array formulas (H2) is the one that is probably slowing you down. I think the SUMIF formulas are pretty fast.

This non-array formula should be faster than the current INDEX-MATCH(IF) array formula in H2.
Code: 
=LOOKUP(-(G2),(1/($A$2:$A$12=F2))*-($D$2:$D$12),$C$2:$C$12)
 
Upvote 0
Columns A:C are your various purchase records. Columns E:G are your unique SKU's and their current inventory. I assumed that purchase records in A:C are sorted chronologically, from oldest to newest. Code loops over unique SKU's in E:E and finds LIFO date.

Code: 
Sub Calc_Inventory_LIFO_Date()

Dim PD As Range, ID As Range
Dim SKU_count As Long, Purch_count As Long
Dim i As Long
Dim OldSearchCell As Range, SearchCell As Range
Dim SearchRng As Range
Dim CurrQty As Long, PurchQty As Long
Dim Purch_Record As Range
Dim Too_Old As Boolean
Const SKU As Integer = 1, Qty As Integer = 2, Dte As Integer = 3

With Application
 .ScreenUpdating = False
 .Calculation = xlCalculationManual

Set PD = Sheet1.Range("Purchase_Data")      'A: SKUID, B: PurchQty, C: Rec_date
Set ID = Sheet1.Range("Inventory_Data")     'E: SKUID, F: CurrQty, G: LIFO_date
SKU_count = ID.Rows.Count
Purch_count = PD.Rows.Count
ID(, Dte).Resize(SKU_count).ClearContents   'clear old LIFO dates

For i = 1 To SKU_count
    CurrQty = ID(i, Qty)
    PurchQty = 0
    Set SearchCell = PD(Purch_count, 1)     'start search from last row
    Too_Old = False
    Set SearchRng = PD.Resize(, SKU)        'search Purch SKUID's
    Do Until PurchQty >= CurrQty
        Set Purch_Record = SearchRng.Find(What:=ID(i, SKU), After:=SearchCell, _
            LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Resize(, 3)
        PurchQty = PurchQty + Purch_Record(, Qty)
        Set OldSearchCell = SearchCell
        Set SearchCell = Purch_Record(1, 1)
        If SearchCell.Row >= OldSearchCell.Row Then 'all purch tallied and still < curr qty
            Too_Old = True
            Exit Do
        End If
    Loop
    ID(i, Dte) = IIf(Too_Old, "IQty > PQty", Purch_Record(, Dte))
Next i

 .ScreenUpdating = True
 .Calculation = xlCalculationAutomatic
End With

End Sub
 
Upvote 0
@Scott R,

I'm trying to use your macro and I get a "Run time error '91': Object Variable or With Block Variable not set" at the following line:

Code: 
Set Purch_Record = SearchRng.Find(What:=ID(i, SKU), After:=SearchCell, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Resize(, 3)

Please let me know how to fix this?

Thanks for your help! apologies for the delayed response
 
Last edited:
Upvote 0
hrithik said:
@Scott R,

I'm trying to use your macro and I get a "Run time error '91': Object Variable or With Block Variable not set" at the following line:

Code: 
Set Purch_Record = SearchRng.Find(What:=ID(i, SKU), After:=SearchCell, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Resize(, 3)
Click to expand...

Hard to say--works for me, but I'm sure your data is arranged differently. Are 'SearchRng' and 'SearchCell' valid ranges when you get to that line? Use the Immediate Window and type "?SearchRng.Address" to validate. Also, my range names 'Purchase_Data' and 'Inventory_Data' do NOT include headers.
 
Upvote 0
@Scott R :
Hard to say--works for me, but I'm sure your data is arranged differently
Click to expand...

Scott, I did NOT include headers in ranges 'Purchase Data' and 'Inventory Data'

Is it possible for you to post your sample excel file online, so I could compare and find where I'm going wrong.....thanks!
 
Upvote 0
Send me your e-mail addr using the Private Message feature and I'll send what I've got.
 
Upvote 0
I have fixed the earlier issue.....but now the code gets stuck at line:

Code: 
    ID(i, Dte) = IIf(Too_Old, "IQty > PQty", Purch_Record(, Dte))

I think its an error in the code because,

Lets say if Curr_Qty is 0, then the code fails as u r reset the Purch_Qty to 0
Code: 
PurchQty = 0
Hence the loop never gets entered
Code: 
 Do Until PurchQty >= CurrQty

Let me know what u think abt this condition.

If the Curr_Qty = 0 then I simply need the last Rcv_date for that SKU.
I really appreciate your time & help on this!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
615
smozgur
smozgur
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
920
epactheactor
E

Forum statistics

Threads
1,224,507
Messages
6,179,176
Members
452,893
Latest member
denay

Share this page

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