Excel VBA需求：指定区域任意单元格为Not Executed时写入SQL Error

Got it, let's fix this VBA code for you! The original issue is that you can't directly check the .Text property of an entire range—it only returns the value of the top-left cell. Here's the corrected version that handles all your requirements perfectly:

Sub Test()
    Dim targetSheet As Worksheet
    Dim indexSheet As Worksheet
    Dim checkRange As Range
    Dim passCount As Long
    Dim notExecutedCount As Long
    
    ' Set references to our worksheets and range to make code cleaner
    Set targetSheet = ThisWorkbook.Worksheets("Before Conversion German Count")
    Set indexSheet = ThisWorkbook.Worksheets("INDEX")
    Set checkRange = targetSheet.Range("G4:G80")
    
    ' Count how many cells are "Not Executed"
    notExecutedCount = WorksheetFunction.CountIf(checkRange, "Not Executed")
    
    If notExecutedCount > 0 Then
        ' Any "Not Executed" means SQL Error
        indexSheet.Range("F30").Value = "SQL Error"
        indexSheet.Range("F30").Interior.ColorIndex = 44
    Else
        ' Count how many cells are "PASS"
        passCount = WorksheetFunction.CountIf(checkRange, "PASS")
        
        ' Check if all cells are PASS using the range's total cell count
        If passCount = checkRange.Cells.Count Then
            indexSheet.Range("F30").Value = "Completed"
            indexSheet.Range("F30").Interior.ColorIndex = 43
        Else
            ' Neither all PASS nor any Not Executed
            indexSheet.Range("F30").Value = "Validation failed"
            indexSheet.Range("F30").Interior.ColorIndex = 3
        End If
    End If
End Sub

Key Improvements & Explanations: #

• Cleaner References: We assign worksheets and the target range to variables first, so you don't have to repeat long worksheet names throughout the code.
• Accurate Range Checks: Using WorksheetFunction.CountIf lets us properly count occurrences of "Not Executed" and "PASS" across the entire range—this fixes the original code's flaw of only checking the first cell.
• Logical Condition Order: We prioritize checking for "Not Executed" first (since it's a critical condition), then verify if every cell is PASS, and fall back to the "Validation failed" case for all other scenarios.
• Dynamic Range Adaptability: Instead of hardcoding the total number of cells in G4:G80, we use checkRange.Cells.Count. If you ever adjust the range (like expanding to G4:G90), the code will automatically update without extra edits.

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