Hi,
In column a i have some stock colours. In Column B are the batch codes for each. In column C are the batch expiry dates and column d is how much stock of each batch we have.
I have my demand in A29:S54.
I have been trying to figure out a way of doing this in VBA but i cannot for the life of me figure it out.
What i want to do is to calculate the Depletion dates in column E by:
Taking the stock figure (so for Purple it would be 2480) then going to the "Purple" row in the demand table (row 36) and from left to right add up the values one at a time. When the cumulative demand exceeds the stock figure, put the relevant date from row 29 into E. So in the example given for purple, the stock figure is 2480 and the demand is 716.8, 1144.9, 1360.6 so it should put 'Sep-23' into E8 as by Aug-23 we would have sold 1861.6 so not sold through the batch yet, but we will in Sept-23. If Cell D = 0 or "-" then leave E blank. Also, if the macro gets to the end of the demand data (Column S) and the total demand is less than the batch stock, then put "Extension Required" into E as that will need a batch extension.
i really hope that all makes sense and any help would be greatly appreciated.
the code i tried is below but this just kept returning Extension Required into all Cells and i cant figure out why.
In column a i have some stock colours. In Column B are the batch codes for each. In column C are the batch expiry dates and column d is how much stock of each batch we have.
I have my demand in A29:S54.
I have been trying to figure out a way of doing this in VBA but i cannot for the life of me figure it out.
What i want to do is to calculate the Depletion dates in column E by:
Taking the stock figure (so for Purple it would be 2480) then going to the "Purple" row in the demand table (row 36) and from left to right add up the values one at a time. When the cumulative demand exceeds the stock figure, put the relevant date from row 29 into E. So in the example given for purple, the stock figure is 2480 and the demand is 716.8, 1144.9, 1360.6 so it should put 'Sep-23' into E8 as by Aug-23 we would have sold 1861.6 so not sold through the batch yet, but we will in Sept-23. If Cell D = 0 or "-" then leave E blank. Also, if the macro gets to the end of the demand data (Column S) and the total demand is less than the batch stock, then put "Extension Required" into E as that will need a batch extension.
i really hope that all makes sense and any help would be greatly appreciated.
the code i tried is below but this just kept returning Extension Required into all Cells and i cant figure out why.
VBA Code:
Sub FindDepletionDate()
Dim ws As Worksheet
Dim demandData As Range
Dim strainData As Range
Dim stockFigures As Range
Dim depletionDateCol As Range
Dim i As Long, j As Long
Dim totalDemand As Double
Dim stock As Double
Dim depletionDate As Variant
Set ws = ThisWorkbook.Sheets("Batch Info") ' Replace "Batch Info" with your sheet name
' Define the ranges for demand data, strain data, stock figures, and depletion date column
Set demandData = ws.Range("A30:S54")
Set strainData = ws.Range("A2:A26")
Set stockFigures = ws.Range("D2:D26")
Set depletionDateCol = ws.Range("E2:E26")
' Loop through each row in the strain data
For i = 1 To strainData.Rows.Count
totalDemand = 0
stock = Val(stockFigures.Cells(i, 1).Value) ' Convert stock to a numeric value
' Check if stock is blank or non-numeric
If Not IsNumeric(stock) Or stock = 0 Then
depletionDateCol.Cells(i, 1).Value = "" ' Leave blank if stock is blank or zero
Else
' Loop through each column in the demand data
For j = 1 To demandData.Columns.Count
totalDemand = totalDemand + demandData.Cells(i + 29, j).Value ' Adjust row index for demandData
' Check if the total demand exceeds or equals the stock figure
If totalDemand >= stock And Not IsEmpty(demandData.Cells(29, j).Value) Then
depletionDate = demandData.Cells(29, j).Value
Exit For ' Exit the inner loop once the condition is met
End If
Next j
' Update the depletion date in the "Depletion Date" column
If Not IsEmpty(depletionDate) Then
depletionDateCol.Cells(i, 1).Value = depletionDate ' Fill in the depletion date
Else
depletionDateCol.Cells(i, 1).Value = "Extension Required" ' Stock not depleted
End If
End If
Next i
End Sub
