excel - 尝试在具有更改行数的过滤数据的表上使用 Vlookup

Question

我已经创建了一个包含过滤数据表的表。在此之后，我想应用 VLookup 命令，使用日期并查找与该日期关联的工单。（这应该每个日期只返回一个工作订单）

这就是我到目前为止所拥有的。我认为我可能遇到的一些问题是在我更改表格的大小之后，因为这个范围随着每个过滤的应用而改变。

            Dim DeliverableTablefltrdRng As Range
            Set DeliverableTablefltrdRng = rRange.SpecialCells(xlCellTypeVisible)
                           
            Dim cel As Range
            Dim DeliverableDate As Date
        
            For Each cel In Range("K2:K" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
                
                'Now we need to verify if the date from the for loop falls
                'between our inputed start and end date
                DeliverableDate = cel
                If ((DeliverableDate >= Start_Date) And (DeliverableDate <= End_Date)) Then
                    'This "if" statement states that we have found a value between the deliverable dates
                    'Next let's record the work order associated for this project
                    
                    'We can achieve this by using a vlookup command
                    workOrder = Application.VLookup(DeliverableDate, DeliverableTablefltrdRng, 6)
                    'DeliverableTablefltrdRngDeliverableTable = Sheet1.Range
                    Debug.Print cel
                    Debug.Print workOrder
                Else
                End If 
             Next cel

标签： excelvba