如何在VBA中逐行读取J/K/L/M/R值并跨表匹配输出结果？

Alright, let's figure out why your current code and COUNTIFS formula aren't working, then fix them to meet your requirement: for each row in Sheet1 (starting at row 2), count matching rows in Sheet2 that match ELR (J), TrackID (K), Rail type (R), and have overlapping mileage ranges with Sheet1's Start/End mileage, then write that count back to Sheet1.

Problem with Your Original VBA Code #

Your existing code has three critical issues preventing it from doing what you need:

1. Hardcoded conditions: It checks for fixed values like "EGM1" and "1100" instead of dynamically reading each row's unique criteria from Sheet1 to search Sheet2.
2. Wrong scope: It only counts rows within Sheet1 that meet those hardcoded rules, not searching Sheet2 at all.
3. Incorrect last row detection: You're using column F to find the last row, but you should use a column guaranteed to have data for every row you need to process (like column J, since it's part of your match criteria).

Fixed VBA Solution #

This code will loop through each row in Sheet1, read the required values, count matching rows in Sheet2, and write the count to column N (adjust this to any column you prefer):

Sub CountSheet2Matches()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastRow1 As Long, i As Long
    Dim elrVal As Variant, trackIdVal As Variant, railTypeVal As Variant
    Dim startMile1 As Double, endMile1 As Double
    Dim matchCount As Long
    
    ' Set explicit references to your worksheets (rename if needed)
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    
    ' Find last row with data in Sheet1's ELR column (J)
    lastRow1 = ws1.Cells(ws1.Rows.Count, "J").End(xlUp).Row
    
    ' Loop through each row in Sheet1 starting at row 2
    For i = 2 To lastRow1
        ' Pull criteria from current Sheet1 row
        elrVal = ws1.Cells(i, "J").Value
        trackIdVal = ws1.Cells(i, "K").Value
        startMile1 = ws1.Cells(i, "L").Value
        endMile1 = ws1.Cells(i, "M").Value
        railTypeVal = ws1.Cells(i, "R").Value
        
        ' Count matching rows in Sheet2:
        ' Matches ELR, TrackID, Rail Type + overlapping mileage ranges
        matchCount = Application.WorksheetFunction.CountIfs( _
            ws2.Range("J:J"), elrVal, _
            ws2.Range("K:K"), trackIdVal, _
            ws2.Range("R:R"), railTypeVal, _
            ws2.Range("L:L"), "<=" & endMile1, _
            ws2.Range("M:M"), ">=" & startMile1 _
        )
        
        ' Write the count to column N in Sheet1 (change column letter as needed)
        ws1.Cells(i, "N").Value = matchCount
    Next i
    
    MsgBox "Matching counts have been calculated!", vbInformation
End Sub

Key Improvements: #

• Explicit worksheet references: Avoids bugs from relying on the active sheet.
• Dynamic criteria reading: Pulls each row's unique values instead of using hardcoded rules.
• Efficient matching: Uses CountIfs to quickly count Sheet2 rows that meet all your criteria, including overlapping mileage ranges.
• Accurate row detection: Uses column J (ELR) to ensure you process every relevant row in Sheet1.

Correct COUNTIFS Formula #

If you prefer a formula over VBA, enter this in cell N2 of Sheet1, then drag it down to all rows:

=COUNTIFS(Sheet2!J:J, J2, Sheet2!K:K, K2, Sheet2!R:R, R2, Sheet2!L:L, "<="&M2, Sheet2!M:M, ">="&L2)

What This Formula Does: #

• Sheet2!J:J, J2: Matches ELR values between Sheet1 and Sheet2
• Sheet2!K:K, K2: Matches TrackID values
• Sheet2!R:R, R2: Matches Rail Type values
• Sheet2!L:L, "<="&M2: Ensures Sheet2's Start Mileage is ≤ Sheet1's End Mileage
• Sheet2!M:M, ">="&L2: Ensures Sheet2's End Mileage is ≥ Sheet1's Start Mileage
• The last two conditions check for overlapping mileage ranges, aligning with the logic hinted at in your original VBA.

Troubleshooting COUNTIFS Returning 0 #

If your test formula returns 0, try these fixes:

1. Data type mismatch: Ensure mileage columns (L and M) are formatted as numbers in both sheets. If stored as text, numeric comparisons fail—fix this via Data > Text to Columns > Finish.
2. Incorrect sheet name: Double-check the sheet name in your formula matches exactly (including spaces/punctuation). For example, if Sheet2 is named Defects (OPEN), use 'Defects (OPEN)'!J:J instead of Sheet2!J:J.
3. Hidden spaces in values: Use TRIM() to remove extra spaces, e.g., TRIM(J2) instead of J2 if values have leading/trailing whitespace.
4. Incomplete criteria: Your original test formula only checked L:L > 1 without matching ELR/TrackID/Rail Type, so returning 0 is expected if no rows meet that single condition.

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