VBA运行时错误'91'求助：对象变量或With块变量未设置

First, let's break down what this error means: Run-time error '91' occurs when you try to use an object variable (like your Range variables) that hasn’t been assigned a valid object via the Set keyword. Let’s walk through the most likely issues based on your code snippets:

1. Unassigned Object Variable #

You declared ProjectList As Range but didn’t show where you initialize it with Set. If you try to use ProjectList anywhere in your code before running Set ProjectList = [some valid range], that’s a direct trigger for this error. Double-check that every object variable (all your Range types here) has a corresponding Set statement before you reference it.

2. Invalid Worksheet Reference #

Your code uses Worksheets("VBA_Data")—make sure this worksheet actually exists in your workbook, and that the name is spelled exactly (capitalization matters in VBA!). If the sheet name is wrong, Worksheets("VBA_Data") returns Nothing, and trying to set a range from it will throw error 91.

Add a quick validation check to avoid this:

Dim vbaSheet As Worksheet
On Error Resume Next
Set vbaSheet = ThisWorkbook.Worksheets("VBA_Data")
On Error GoTo 0

If vbaSheet Is Nothing Then
    MsgBox "Worksheet 'VBA_Data' not found!", vbExclamation
    Exit Sub
End If

Then use vbaSheet instead of Worksheets("VBA_Data") in your code to eliminate this risk.

3. Invalid Range from Empty Column H #

If column H in "VBA_Data" is completely empty, Cells(Rows.Count, 8).End(xlUp).Row returns 1 (landing on the header row H1). This makes your range H2:H1—an invalid range where the start row is greater than the end row. Trying to set EngineAncillariesProjectsVBAList to this will cause error 91.

Fix this by adding a check for valid data rows:

EngineAncillariesProjectsLastRow = vbaSheet.Cells(vbaSheet.Rows.Count, 8).End(xlUp).Row

' Ensure we have at least one data row (H2 or beyond)
If EngineAncillariesProjectsLastRow < 2 Then
    MsgBox "No data found in column H of 'VBA_Data'!", vbExclamation
    Exit Sub
End If

Set EngineAncillariesProjectsVBAList = vbaSheet.Range("H2:H" & EngineAncillariesProjectsLastRow)

4. Typo in Variable Name #

Your code snippet cuts off at Engin...—if you have a typo in EngineAncillariesProjectsLastRow when building the range (e.g., missing a letter), VBA might treat the misspelled variable as uninitialized (or zero), leading to an invalid range like H2:H0. Double-check all variable names in that line for typos.

Quick Debugging Trick #

To pinpoint the exact problematic line:

• Open the VBA Editor (Alt+F11)
• Go to Tools > Options
• Check "Break on All Errors"
• Run your code again—it will stop directly on the line triggering the error, making it easy to see which object isn’t set properly.

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