基于VLOOKUP返回值的单元格对比、循环处理及错误值适配需求

Got it, let's work through your requirements to get that VBA code working exactly how you need it. You've got two key goals: looping through rows to handle results from two VLOOKUP cells, and comparing those values while catching errors like #N/A or #VALUE to return "Not submitted". Here's how to do it:

First: Add Error Detection for VLOOKUP Errors #

Your original code doesn't account for Excel errors like #N/A (when VLOOKUP can't find a match) or #VALUE (invalid input for VLOOKUP). We'll use IsError() to catch these right away before processing the values.

Second: Loop Through Rows & Compare Values #

We'll iterate through each row, check both VLOOKUP result cells for errors first. If there's any error in either cell, we set the third cell to "Not submitted". If no errors are found, we compare the two values and output your desired result based on your rules.

Updated VBA Code #

Sub ProcessVLOOKUPResults()
    Dim i As Long
    Dim lastRow As Long
    Dim vlookupCell1 As Range, vlookupCell2 As Range
    Dim outputCell As Range
    
    ' Get the last row with data in your VLOOKUP column (adjust column letter if needed)
    lastRow = Cells(Rows.Count, "N").End(xlUp).Row
    
    ' Loop from row 2 (assuming header is row 1) to the last row
    For i = 2 To lastRow
        ' Define your two VLOOKUP result cells and output cell
        Set vlookupCell1 = Range("N" & i) ' First VLOOKUP output cell
        Set vlookupCell2 = Range("O" & i) ' Second VLOOKUP output cell
        Set outputCell = Range("P" & i) ' Third cell for final results
        
        ' Check if either cell has an error (#N/A, #VALUE, etc.)
        If IsError(vlookupCell1) Or IsError(vlookupCell2) Then
            outputCell.Value = "Not submitted"
        Else
            ' Customize this comparison logic to fit your exact needs
            ' Option 1: Match your original range check (use either VLOOKUP cell)
            ' If vlookupCell1.Value >= 0 And vlookupCell1.Value <= 10 Then
            '     outputCell.Value = "Within SLA"
            ' ElseIf vlookupCell1.Value >= 11 Then
            '     outputCell.Value = "Exceed SLA"
            ' Else
            '     outputCell.Value = "Not submitted"
            ' End If
            
            ' Option 2: Directly compare the two VLOOKUP values
            If vlookupCell1.Value = vlookupCell2.Value Then
                outputCell.Value = "Values match"
            ElseIf vlookupCell1.Value > vlookupCell2.Value Then
                outputCell.Value = "First value is larger"
            Else
                outputCell.Value = "Second value is larger"
            End If
        End If
    Next i
End Sub

Quick Tips to Tailor This to Your Sheet: #

• Adjust Cell References: Change N, O, and P to the actual columns where your VLOOKUPs are stored and where you want the final results to appear.
• Pick the Right Comparison: Use Option 1 if you need to check a single VLOOKUP result against a numeric range (like your original code), or Option 2 if you need to compare the two VLOOKUP values directly. Feel free to rewrite this section to match your specific business rules.
• Last Row Calculation: I used column N to find the last row with data, but if you already have a variable like NRScount75 defined elsewhere, just replace lastRow with that variable.
• Error Coverage: The IsError() function catches all Excel error types, so it will handle #N/A and #VALUE exactly as you require.

How to Implement This: #

1. Open your Excel file, press Alt + F11 to open the VBA Editor.
2. Right-click your workbook in the Project pane > Insert > Module.
3. Paste the code into the new module.
4. Tweak the cell references and comparison logic to fit your sheet's structure.
5. Run the macro by pressing F5 in the editor, or add a button in Excel to run it with one click.

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