VBA运行时错误'91'求助：对象变量未设置，工作表引用异常？

Hey there! Let's walk through why you're hitting that run-time error '91' and fix up your code properly.

Why the Error Happens #

The error on line 8 is directly caused by your ws variable being Nothing—you declared it as a Worksheet but never assigned it to an actual worksheet. Your With Sheets("Overall Stats").Select line only selects the sheet, not links it to the ws variable. When you try to use ws.Range(...), there's no valid worksheet object attached to ws, hence the error.

Other Issues in Your Code #

Beyond the main error, there are a few other problems that will cause bugs or inefficiencies:

• Malformed last row calculation: ws.Range("G3" & Rows.Count) creates an invalid range like G31048576 instead of targeting the last used row in column G.
• Misplaced block structure: Your End With and End If statements are out of order, breaking the code's logic flow.
• Unnecessary Select/Selection: This slows down your code and makes it prone to errors if the active sheet changes.
• Loop starting at row 1: If your data starts at row 3 (as seen in your range references), looping from row 1 checks empty cells unnecessarily.

Corrected Code #

Here's the fixed version with explanatory comments:

Sub ConditionalFormat()
    Dim LR As Long, i As Long
    Dim ws As Worksheet
    
    ' Assign the worksheet to the ws variable (critical fix for error 91)
    Set ws = ThisWorkbook.Sheets("Overall Stats")
    
    ' Calculate the last used row in column G (correct syntax)
    LR = ws.Range("G" & ws.Rows.Count).End(xlUp).Row
    
    ' Loop from row 3 to the last row (matches your data starting point)
    For i = 3 To LR
        ' Check if column G in current row equals "ISO2"
        If ws.Range("G" & i).Value = "ISO2" Then
            ' Define the target range in column L (from L3 to last used row)
            Dim targetRange As Range
            Set targetRange = ws.Range("L3:L" & ws.Range("L" & ws.Rows.Count).End(xlUp).Row)
            
            ' Clear existing conditional formats first (optional but clean)
            targetRange.FormatConditions.Delete
            
            ' Add the 3-color scale conditional format
            targetRange.FormatConditions.AddColorScale ColorScaleType:=3
        End If
    Next i
End Sub

Key Fixes Breakdown #

1. Set ws = ThisWorkbook.Sheets("Overall Stats"): This properly links your ws variable to the actual worksheet, eliminating the "Object variable not set" error.
2. Correct last row calculation: Using ws.Rows.Count ensures you're targeting the last row of the correct worksheet, not the active sheet.
3. Removed Select/Selection: Directly referencing ranges makes the code faster and more reliable.
4. Fixed block structure: Properly nested If/End If and removed the confusing outer With block.
5. Adjusted loop start: Starting at row 3 skips empty top rows and aligns with your data's starting point.

内容的提问来源于stack exchange，提问作者Mike F.