Date of Receipt based on In/OUT

**cmcconna** · 01-29-2010, 07:24 AM

HI all---- (Altered post to try and explain further)

Looking for some help, Think Ive bitten of more than I can chew with this one!!!

I have two worksheets, one contains Yesterday information - Stock and Date of receipt, the other contains todays information without Date of receipt.

I need to update the todays worksheet with Date of receipt information from the yesterdays Sheets.. sounds simple?

The problem is im working on an aging profile for stock (done manually at the moment) and the Todays sheet has a total quantity per part where as the Yesterday sheet has the part broken down to show the date of receipt by quantity.

For example
Part number A has one entry totaling 20 pcs on Todays Sheet but has 3 entries on yesterdays 10, 5, 5 = 20 in total. The three entries have different dates of receipt.

I need to update todays sheet to split Part number A into the three entires with the 3 dates.

As this is a aging profile Im working on I also need to factor in any shipments

For example
Part number B has one entry totaling 10 pcs - Yesterdays sheet has 3 entries 5 (DOR 01/12/2009), 5 (DOR 01/01/2010), 5 (DOR 20/01/2010) = 15 each with different dates

I need to ignore the oldest entry on Yesterdays sheet (as the oldst part has shipped, therefore Todays sheet would only show 2 entries of 5 with the 01/01/2010 & 20/01/2010

Also if a part is on TOdays and not on YEsterdays sheet then automaticaly enter todays date.

Also if a part is on yesterdays but not on Todays then add that part to todays to allow for output calculation...

Any help/ideas would be appreciated.......Ive been through the site gathering snipets of code, but not really sure where to start to put it all together!

**cmcconna** · 01-30-2010, 09:31 AM

Does anyone know of any similar xls examples i could look at?

Thanks all

**cmcconna** · 01-31-2010, 06:47 PM

Originally Posted by cmcconna

Does anyone know of any similar xls examples i could look at?

Thanks all

Hi all, i was shocked no one wanted to help here! point me in the right direction, but anyway...

Ive had a bash and have came up with the following... yes I can imagine it could be so much better, but this is about my limit on VBA.

I created two main scripts, the first to combine the data from Yesterday and today onto one sheet.

The second Script calculates the stock and removes the oldest previous quantities first if it find any shipment have happened

Anyways the sceond script is below - feel free to offer any imprvements on the way I have done this. always happy to learn.

Sub Aged_update()

Dim C1, C2, C3, C4, C5, C6 As String
Dim R1, R2 As Long


Dim r As Range
Lnglastrow = Cells(Rows.Count, "B").End(xlUp).Row
Set Rng = Range("B2:B" & Lnglastrow)

For Each i In Rng
i.Select

Select Case i.Offset(0, 3).Value

Case Is = ""



Case Is = 0 '======= New Inventory not on yesterdays Aged Report , Date set to Today - 1
i.Offset(0, 2).Value = Format("=TODAY()-1", "dd / mm / yyy")
i.Offset(0, 3).Value = ""
i.Offset(0, 4).Value = 0



Case Is = 1 '======= Material appear on yesterdays aged report with 1 entry (only one Date of receipt)
C1 = i.Offset(0, 1).Value
C2 = i.Offset(1, 1).Value
R2 = C1 - C2

'======= Checks if todays Quantites  match with yesterdays, if Yes deletes line and leaves yesterday entry on report.
If C1 = C2 Then
i.Offset(1, 4).Value = R2
i.EntireRow.Delete

Else


'======= Check if Todays is Greater than yesterdays, if Yes calculates the new input and sets it as todays date
If C1 > C2 Then
C1 = C1 - C2
i.Offset(0, 1).Value = C1
i.Offset(0, 2).Value = Format("=TODAY()-1", "dd / mm / yyy")
i.Offset(0, 3).Value = ""
i.Offset(0, 4).Value = R2
'i.Offset(1, 4).Value = R2

Else


'======= Checks to see if Todays Value is less than yesterdays (ships). Sets yesterday entry to todays qunatity keeping the same date.
If C1 < C2 Then
i.Offset(1, 1).Value = C1
i.Offset(1, 4).Value = R2
i.EntireRow.Delete


End If
End If
End If


Case Is = 2
C1 = i.Offset(0, 1).Value   ' Todays total Qunatity
C2 = i.Offset(1, 1).Value   ' YEsterdays 1 line Quantity
C3 = i.Offset(2, 1).Value   ' Yesterdays 2 line quantity
R1 = C2 + C3
R2 = C1 - R1

'======= Checks if todays Quantites  match with yesterdays, if Yes deletes line and leaves yesterday entry on report.
If C1 = R1 Then
i.Offset(1, 4).Value = R2
i.Offset(2, 4).Value = R2

i.EntireRow.Delete
Else


'======= Check if Todays is Greater than yesterdays, if Yes calculates the new input and sets it as todays date
If C1 > R1 Then
C1 = C1 - R1
i.Offset(0, 1).Value = C1
i.Offset(0, 2).Value = Format("=TODAY()-1", "dd / mm / yyy")
i.Offset(0, 3).Value = ""
i.Offset(0, 4).Value = R2
Else

' Today is Less than yesterday
'======= Checks to see if Todays Value is less than yesterdays (ships). Sets yesterday entry to todays qunatity keeping the same date.
R2 = R1 - C1   ' R2 = Ships
If C2 >= R2 Then
C2 = C2 - R2
i.Offset(1, 1).Value = C2
i.EntireRow.Delete
Else
If C2 + C3 >= R2 Then
R2 = R2 - C2
C3 = C3 - R2
i.Offset(2, 1).Value = C3
i.Offset(1, 0).EntireRow.Delete
i.EntireRow.Delete

Else

End If
End If
End If
End If

Case Is = 3

C1 = i.Offset(0, 1).Value   ' Todays total Qunatity
C2 = i.Offset(1, 1).Value   ' YEsterdays 1 line Quantity
C3 = i.Offset(2, 1).Value   ' Yesterdays 2 line quantity
C4 = i.Offset(3, 1).Value   ' Yesterdays 3 Line Quantity
R1 = C2 + C3 + C4
R2 = C1 - R1
'======= Checks if todays Quantites  match with yesterdays, if Yes deletes line and leaves yesterday entry on report.
If C1 = R1 Then
i.Offset(1, 4).Value = R2
i.EntireRow.Delete
Else


'======= Check if Todays is Greater than yesterdays, if Yes calculates the new input and sets it as todays date
If C1 > R1 Then
C1 = C1 - R1
i.Offset(0, 1).Value = C1
i.Offset(0, 2).Value = Format("=TODAY()-1", "dd / mm / yyy")
i.Offset(0, 3).Value = ""
i.Offset(0, 4).Value = R2
Else

' Today is Less than yesterday
'======= Checks to see if Todays Value is less than yesterdays (ships). Sets yesterday entry to todays qunatity keeping the same date.
R2 = R1 - C1   ' R2 = Ships
If C2 >= R2 Then
C2 = C2 - R2
i.Offset(1, 4).Value = C1 - R1
i.Offset(1, 1).Value = C2
i.EntireRow.Delete
Else
If C2 + C3 >= R2 Then
i.Offset(1, 0).EntireRow.Delete '  Delete Row 1 from yesterday
R2 = R2 - C2
C3 = C3 - R2
i.Offset(1, 4).Value = C1 - R1
i.Offset(1, 1).Value = C3
'i.Offset(1, 0).EntireRow.Delete
i.EntireRow.Delete

Else
If C2 + C3 + C4 >= R2 Then
i.Offset(1, 0).EntireRow.Delete '  Delete Row 1 from yesterday
i.Offset(1, 0).EntireRow.Delete '  Delete Row 1 from yesterday
R2 = R2 - C2 - C3
C4 = C4 - R2
i.Offset(1, 4).Value = C1 - R1
i.Offset(1, 1).Value = C4
i.EntireRow.Delete


End If
End If
End If
End If
End If

Case Is = 4

C1 = i.Offset(0, 1).Value   ' Todays total Qunatity
C2 = i.Offset(1, 1).Value   ' YEsterdays 1 Line Quantity
C3 = i.Offset(2, 1).Value   ' Yesterdays 2 Line quantity
C4 = i.Offset(3, 1).Value   ' Yesterdays 3 Line Quantity
C5 = i.Offset(4, 1).Value   ' Yesterdays 4 Line Quantity
R1 = C2 + C3 + C4 + C5
R2 = C1 - R1
'======= Checks if todays Quantites  match with yesterdays, if Yes deletes line and leaves yesterday entry on report.
If C1 = R1 Then
i.Offset(1, 4).Value = R2
i.EntireRow.Delete
Else


'======= Check if Todays is Greater than yesterdays, if Yes calculates the new input and sets it as todays date
If C1 > R1 Then
C1 = C1 - R1
i.Offset(0, 1).Value = C1
i.Offset(0, 2).Value = Format("=TODAY()-1", "dd / mm / yyy")
i.Offset(0, 3).Value = ""
i.Offset(0, 4).Value = R2
Else

' Today is Less than yesterday
'======= Checks to see if Todays Value is less than yesterdays (ships). Sets yesterday entry to todays qunatity keeping the same date.
R2 = R1 - C1   ' R2 = Ships
If C2 >= R2 Then
C2 = C2 - R2
i.Offset(1, 4).Value = C1 - R1
i.Offset(1, 1).Value = C2
i.EntireRow.Delete
Else
If C2 + C3 >= R2 Then
i.Offset(1, 0).EntireRow.Delete '  Delete Row 1 from yesterday
R2 = R2 - C2
C3 = C3 - R2
i.Offset(1, 4).Value = C1 - R1
i.Offset(1, 1).Value = C3
'i.Offset(1, 0).EntireRow.Delete
i.EntireRow.Delete

Else
If C2 + C3 + C4 >= R2 Then
i.Offset(1, 0).EntireRow.Delete '  Delete Row 1 from yesterday
i.Offset(1, 0).EntireRow.Delete '  Delete Row 1 from yesterday
R2 = R2 - C2 - C3
C4 = C4 - R2
i.Offset(1, 4).Value = C1 - R1
i.Offset(1, 1).Value = C4
i.EntireRow.Delete

Else
If C2 + C3 + C4 + C5 >= R2 Then
i.Offset(1, 0).EntireRow.Delete '  Delete Row 1 from yesterday
i.Offset(1, 0).EntireRow.Delete '  Delete Row 1 from yesterday
i.Offset(1, 0).EntireRow.Delete '  Delete Row 1 from yesterday
R2 = R2 - C2 - C3 - C4
C5 = C5 - R2
i.Offset(1, 4).Value = C1 - R1
i.Offset(1, 1).Value = C5
i.EntireRow.Delete

End If
End If
End If
End If
End If
End If

Case Is = 5
C1 = i.Offset(0, 1).Value   ' Todays total Qunatity
C2 = i.Offset(1, 1).Value   ' YEsterdays 1 Line Quantity
C3 = i.Offset(2, 1).Value   ' Yesterdays 2 Line quantity
C4 = i.Offset(3, 1).Value   ' Yesterdays 3 Line Quantity
C5 = i.Offset(4, 1).Value   ' Yesterdays 4 Line Quantity
C6 = i.Offset(5, 1).Value   'Yesterdays 5 Line Qunatity
R1 = C2 + C3 + C4 + C5 + C6
R2 = C1 - R1

'======= Checks if todays Quantites  match with yesterdays, if Yes deletes line and leaves yesterday entry on report.
If C1 = R1 Then
i.Offset(1, 4).Value = R2
i.EntireRow.Delete
Else


'======= Check if Todays is Greater than yesterdays, if Yes calculates the new input and sets it as todays date
If C1 > R1 Then
C1 = C1 - R1
i.Offset(0, 1).Value = C1
i.Offset(0, 2).Value = Format("=TODAY()-1", "dd / mm / yyy")
i.Offset(0, 3).Value = ""
i.Offset(0, 4).Value = R2
Else

' Today is Less than yesterday
'======= Checks to see if Todays Value is less than yesterdays (ships). Sets yesterday entry to todays qunatity keeping the same date.
R2 = R1 - C1   ' R2 = Ships
If C2 >= R2 Then
C2 = C2 - R2
i.Offset(1, 4).Value = C1 - R1
i.Offset(1, 1).Value = C2
i.EntireRow.Delete
Else
If C2 + C3 >= R2 Then
i.Offset(1, 0).EntireRow.Delete '  Delete Row 1 from yesterday
R2 = R2 - C2
C3 = C3 - R2
i.Offset(1, 4).Value = C1 - R1
i.Offset(1, 1).Value = C3
'i.Offset(1, 0).EntireRow.Delete
i.EntireRow.Delete

Else
If C2 + C3 + C4 >= R2 Then
i.Offset(1, 0).EntireRow.Delete '  Delete Row 1 from yesterday
i.Offset(1, 0).EntireRow.Delete '  Delete Row 1 from yesterday
R2 = R2 - C2 - C3
C4 = C4 - R2
i.Offset(1, 4).Value = C1 - R1
i.Offset(1, 1).Value = C4
i.EntireRow.Delete

Else
If C2 + C3 + C4 + C5 >= R2 Then
i.Offset(1, 0).EntireRow.Delete '  Delete Row 1 from yesterday
i.Offset(1, 0).EntireRow.Delete '  Delete Row 1 from yesterday
i.Offset(1, 0).EntireRow.Delete '  Delete Row 1 from yesterday
R2 = R2 - C2 - C3 - C4
C5 = C5 - R2
i.Offset(1, 4).Value = C1 - R1
i.Offset(1, 1).Value = C5
i.EntireRow.Delete

Else
If C2 + C3 + C4 + C5 + C6 >= R2 Then
i.Offset(1, 0).EntireRow.Delete '  Delete Row 1 from yesterday
i.Offset(1, 0).EntireRow.Delete '  Delete Row 1 from yesterday
i.Offset(1, 0).EntireRow.Delete '  Delete Row 1 from yesterday
i.Offset(1, 0).EntireRow Delete ' Delete Row 1 From Yesterday
R2 = R2 - C2 - C3 - C4 - C5
C6 = C6 - R2
i.Offset(1, 4).Value = C1 - R1
i.Offset(1, 1).Value = C6
i.EntireRow.Delete


End If
End If
End If
End If
End If
End If
End If

Case Else
End Select

Next i
End Sub