Compare Demand Data to Stock Data

[hillmas]

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.

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

 

[Bosquedeguate]

Please provide the example rows that support your problem description.
Row 29 through Row 36 with column headers if you have them.

 

[hillmas]

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.

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

***apologies...see attached picture

 

[Bosquedeguate]

Try modifying your code a small bit. The logic appears to be OK.

The value in your top table for Purple stock is 0.024806

But, your depletion values are in larger units 716.8, 1449.9, 1360.6

In your code multiply your stock value by 10000 or divide your depletion rates by 10000.

Your stock units and depletion rate units do not match.

 

[Bosquedeguate]

'change this line of code
stock = Val(stockFigures.Cells(i, 1).Value) 
'to
stock = Val(stockFigures.Cells(i, 1).Value) *10000

 

[hillmas]

Hi Bosquedeguate,

Many thanks for your suggestion however it did not work and it still just puts 'Extension Required' for all lines.

regards,
M

 

[Bosquedeguate]

Did you try other multipliers? 10k looked like what was needed.

Can you post a mini-sheet with some test data?