Macro to calculate lead time for orders in a table

[iLiedOnMyResume17]

I’m trying to come up with a macro that will take a extremely long list of orders (shown below) and add a column that shows lead time (order delivery date - placement date).

The issue is that there is no specific value for delivery date. There’s a column for how many units need to be delivered at the first of the each month.

I know how to do it the brute force way, “if value in July column > 0, then that’s the delivery date”, I need a macro that others can used but can’t figure it out.

All values are originally formatted as text and the first non-zero unit is the delivery date. The table more than likely will have future orders appended at the bottom.

I’m interning so I have limited time to figure this out, someone please help.

 

[brookep5842]

I’m trying to come up with a macro that will take a extremely long list of orders (shown below) and add a column that shows lead time (order delivery date - placement date).

The issue is that there is no specific value for delivery date. There’s a column for how many units need to be delivered at the first of the each month.

I know how to do it the brute force way, “if value in July column > 0, then that’s the delivery date”, I need a macro that others can used but can’t figure it out.

All values are originally formatted as text and the first non-zero unit is the delivery date. The table more than likely will have future orders appended at the bottom.

I’m interning so I have limited time to figure this out, someone please help.

Ignore previous reply haha

Hello ~ If you haven’t gotten fired yet, I have a solution that should work based on my understanding.

• I didn’t include the delivery date and placement date in the lead time calculation but it can be easily changed, I made a comment about it.
• I based the starting/default column and row numbers for all the variables based on the table in the image you attached. If you added an extra header row or something it would affect the code.
• I reference the data as a table on one line (the 2021/2022 row and Date row aren’t part of the named table) so, give it a name as an official table. And make sure a new 'Lead time' column to the RIGHT of the June column is included in that table. Lastly, change "Sheet1" where it appears to your sheet name.

Full disclosure, I'm pretty new at VBA so making a duplicate of your worksheet just in case would be a good idea!!

Sub return_leadtime()

    Dim rng_cell As Range
    Dim rng_search As Range
    
    Dim lng_row As Long
    Dim placementdate As Date
    Dim str_month As String
    Dim int_year As Integer
    Dim deliverydate As Date
    Dim leadtime As Long
    Dim lng_col As Long
    Dim col_letter As Long
    Dim leadoff As Long
    

    lng_row = 4
    lead_off = 3
    place_row = 4


    For Each rng_cell In Sheets("Sheet1").ListObjects("Table1"). _
    ListColumns("July").DataBodyRange

    
       'this line skips rows with a lead time already calculated
       'to rewrite existing leadtime values, comment it out
        If rng_cell.Offset(0, 12).Value > 0 Then GoTo hophop
    
    
        For Each rng_search In Worksheets("Sheet1").Range("C" & lng_row & ":" & "N" & lng_row)
                          
            If rng_search.Value > 0 Then
            Exit For
            
            ElseIf rng_search.Value <= 0 Then lng_row = lng_row + 1
                                                        
            End If
        
        
        Next rng_search
          
                          
        lng_col = rng_search.Column
        
        str_month = Cells(3, lng_col).Value
    
  
        If Not IsError(Application.Match(str_month, Range("C3:H3"), 0)) Then int_year = 2021
        
        If Not IsError(Application.Match(str_month, Range("I3:N3"), 0)) Then int_year = 2022
        
                  
        deliverydate = Format(str_month & "/" & "1" & "/" & int_year, "mm-dd-yyyy")
                  
        placementdate = Cells(place_row, 1).Value
        
        
        'the -2 means the placement date and delivery dates are not counted in lead time result
        'if you want the delivery and placement dates counted, delete the -2 from the end
        leadtime = DateDiff("d", placementdate, deliverydate) - 2
        
        If WorksheetFunction.Sum(Range("C" & place_row & ":" & "N" & place_row)) = 0 Then leadtime = 0
        
        ThisWorkbook.Sheets("Sheet1").Range("O1").Offset(lead_off, 0) = leadtime
        
hophop:
        
        place_row = place_row + 1
        lead_off = lead_off + 1
      
      
    Next rng_cell


End Sub