多日期场景下Filter与Loop的使用问题咨询

Hey there! Let’s work through your Filter and Loop problem step by step—this is a common scenario with inventory and delivery date data, so we’ll get it sorted out.

First: Fix the "No Results" Issue with Your Filter #

Let’s start with why your current filter isn’t returning any data—here are the most common culprits and quick fixes:

• Date Type Mismatch: Make sure both your Delivery Date (A590 column) and inventory date fields are stored as actual date types (not text strings). If they’re text, convert them first:
  • In SQL: Use CONVERT(date, your_column)
  • In Excel/VBA: Use CDate() to turn text into a proper date value
• Incorrect "Day Before" Calculation: Double-check how you’re calculating the prior day. Edge cases like month/year transitions (e.g., Jan 1 → Dec 31 of the previous year) can break simple subtraction. Try:
  • SQL: DATEADD(day, -1, delivery_date) instead of delivery_date -1
  • Excel/VBA: delivery_date -1 works, but confirm the cell is formatted as a date (not a raw number)
• Missing Inventory Data: Verify your inventory masterdata actually has records for the day before at least one delivery date. Pick a delivery date, compute the prior day manually, and search your inventory table to confirm data exists—if there’s nothing there, that’s why your filter returns empty results!

Second: Loop Through All Target Dates (Up to Year-End) #

Instead of handling dates one by one, we can automate this. The best approach depends on whether you’re using SQL (database) or Excel/VBA (spreadsheet):

Option 1: Set-Based Approach (Recommended for SQL) #

If you’re working with a database, skip loops when you can—set-based queries are faster and more reliable. Here’s how to pull all delivery dates (up to year-end) along with their prior day’s inventory:

-- Replace with your actual table/column names
SELECT
  d.delivery_date,
  DATEADD(day, -1, d.delivery_date) AS inventory_date,
  i.* -- Swap * for specific inventory columns (e.g., i.stock_level) if needed
FROM
  (
    -- Get all unique delivery dates from your A590 column, filtered to year-end
    SELECT DISTINCT delivery_date
    FROM your_delivery_table
    WHERE delivery_date <= DATEFROMPARTS(YEAR(GETDATE()), 12, 31)
  ) d
LEFT JOIN
  your_inventory_masterdata i
ON
  i.inventory_date = DATEADD(day, -1, d.delivery_date)
ORDER BY
  d.delivery_date;

This query first grabs all unique delivery dates up to December 31 of the current year, then joins them with inventory data from the day before. Using LEFT JOIN ensures you still see delivery dates even if there’s no inventory data for the prior day (you’ll get NULL values for inventory fields, which you can handle with COALESCE if needed).

Option 2: Loop Approach (For Excel VBA) #

If you’re working in Excel, here’s a VBA script to loop through each delivery date in column A (starting at A590) and pull the prior day’s inventory:

Sub GetPriorDayInventory()
    Dim deliveryDate As Date
    Dim priorDayDate As Date
    Dim lastRow As Long
    Dim i As Long
    Dim inventoryWS As Worksheet
    Dim deliveryWS As Worksheet
    
    ' Update these to match your worksheet names
    Set deliveryWS = ThisWorkbook.Worksheets("DeliveryDates")
    Set inventoryWS = ThisWorkbook.Worksheets("InventoryMasterdata")
    
    ' Find the last row with delivery dates in column A
    lastRow = deliveryWS.Cells(deliveryWS.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each delivery date starting at A590
    For i = 590 To lastRow
        deliveryDate = deliveryWS.Cells(i, "A").Value
        
        ' Skip empty cells or non-date values
        If IsDate(deliveryDate) Then
            priorDayDate = deliveryDate - 1
            
            ' Stop if we go past year-end
            If priorDayDate > DateSerial(Year(Date), 12, 31) Then
                Exit For
            End If
            
            ' Filter inventory for the prior day (adjust Field number to your date column)
            inventoryWS.Range("A1").AutoFilter Field:=1, Criteria1:=priorDayDate
            
            ' Copy results to column B of the delivery sheet (adjust as needed)
            inventoryWS.AutoFilter.Range.Copy deliveryWS.Cells(i, "B")
        End If
    Next i
    
    ' Turn off the filter when done
    inventoryWS.AutoFilterMode = False
End Sub

Quick notes for this script:

• Change the worksheet names (DeliveryDates and InventoryMasterdata) to match your file
• Adjust Field:=1 if your inventory date column isn’t the first column in the inventory sheet
• The script copies results to column B—modify the deliveryWS.Cells(i, "B") part to send data wherever you need it

Final Quick Checks #

• Test with a delivery date where you know inventory exists for the prior day to confirm results show up
• If dates still don’t match, check for hidden time components (e.g., 2024-05-20 14:30:00 vs 2024-05-20). In SQL, use CAST(inventory_date AS date) to remove time; in Excel, format cells to show only dates

内容的提问来源于stack exchange，提问作者sheela