VBA跨工作簿数据查询异常：price/leadtime始终返回0求助

Hey there, let's break down why your macro is stuck returning 0 for leadtime and price. I've looked over your code and spotted a couple critical issues that are causing this behavior:

Key Issues in Your Code #

• Missing Worksheet Reference: When you use With Workbooks("U100 Material Information.xlsx") and then loop through Range("A:A"), VBA defaults to searching the active worksheet (not necessarily the one in your U100 file). This means your macro is probably looking in the wrong sheet entirely, so it never finds a match—leaving leadtime and price stuck at their default 0 value.
• Incorrect Offset Calculation: You referenced a.Offset(,13) for leadtime and a.Offset(,15) for price, but offset counts from the starting column (A=1). Column 13 is actually Offset(,12) (13-1=12), and column 15 is Offset(,14) (15-1=14). This hidden mistake was likely returning empty values that defaulted to 0.
• No Variable Reset: You declare leadtime and price once at the top of the sub. If a previous row found a match, these variables would retain that value for the next row; if no match is found, they stay at 0.
• Inefficient Full-Column Loop: Traversing every cell in column A is slow, especially with large datasets. We can fix this by stopping the loop as soon as a match is found.

Fixed Code #

Here's the revised version of your macro with all these issues addressed:

Sub MMRFValidation()
    Dim c As Range
    Dim leadtime As Double
    Dim price As Double
    Dim u100WS As Worksheet ' Define worksheet variable for clarity
    
    Application.ScreenUpdating = False
    
    ' Set reference to the specific worksheet in U100 file (update sheet name if needed!)
    Set u100WS = Workbooks("U100 Material Information.xlsx").Worksheets("Sheet1")
    
    With Workbooks("Job MMRF.csv").Worksheets("Sheet1") ' Also specify sheet for MMRF file
        For Each c In .Range("C:C")
            ' Skip empty cells in column C (optional optimization)
            If c.Value = "" Then
                c.Offset(, -2).Font.Color = vbRed
                c.Offset(, 9).Value = "Need to contact vendor"
                c.Offset(, 10).Value = "Need to contact vendor"
            Else
                ' Reset variables to 0 for each new material
                leadtime = 0
                price = 0
                
                ' Search only used range in U100 column A (faster than full column)
                For Each a In u100WS.Range("A1:A" & u100WS.Cells(u100WS.Rows.Count, "A").End(xlUp).Row)
                    If a.Value = c.Value Then
                        price = a.Offset(, 14).Value ' Corrected offset for column 15
                        leadtime = a.Offset(, 12).Value ' Corrected offset for column 13
                        Exit For ' Stop searching once match is found
                    End If
                Next a
                
                ' Your original logic (adjust if needed)
                If price = 0.01 And leadtime = 21 Then
                    c.Offset(, -2).Font.ColorIndex = 7
                    c.Offset(, 9).Value = leadtime
                    c.Offset(, 10).Value = price
                Else
                    c.Offset(, -2).Font.Color = vbGreen
                    c.Offset(, 9).Value = leadtime
                    c.Offset(, 10).Value = price
                End If
            End If
        Next c
    End With
    
    Application.ScreenUpdating = True
End Sub

Critical Notes About the Fixes #

1. Explicit Worksheet References: I added clear references to the worksheets in both files (update "Sheet1" to your actual sheet names!). This ensures VBA looks in the right place every time.
2. Offset Correction: Fixed the offset values to match your target columns (13 and 15) correctly—this was a silent bug that was likely returning empty values.
3. Variable Reset: We reset leadtime and price to 0 at the start of each iteration for a new material, so old values don't carry over to subsequent rows.
4. Efficient Range: Instead of looping the entire column A, we only loop the used range (up to the last row with data) which speeds up the macro significantly.
5. Exit For: Once a match is found, we stop searching the U100 list—no need to check every remaining cell.

Bonus Optimization (Even Faster) #

For large datasets, using VLookup instead of a loop will be way more efficient. Here's how you could replace the inner loop:

' Replace the inner For Each loop with this:
leadtime = Application.VLookup(c.Value, u100WS.Range("A:M"), 13, False)
price = Application.VLookup(c.Value, u100WS.Range("A:O"), 15, False)

' Handle cases where no match is found (VLookup returns #N/A)
If IsError(leadtime) Then leadtime = 0
If IsError(price) Then price = 0

This cuts out the entire inner loop and makes the macro run in a fraction of the time.

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