VBA创建Data工作表后提示“Subscript out of range”错误求助

错误原因分析 #

你遇到的"Subscript out of range"错误，核心原因是创建工作表时未明确指定归属的工作簿：默认Sheets.Add会将新表添加到当前激活的工作簿中，若运行宏时存在其他打开的工作簿，新表可能不在宏所在的ThisWorkbook里，后续用ThisWorkbook.Sheets("Data")自然找不到目标工作表。

同时代码还有两个隐性问题：

• 未声明循环变量i，违反VBA变量声明规范，可能引发未知错误
• LastRow计算逻辑错误：刚创建的Data表仅第3行有标题，前两行为空，.Cells(.Rows.Count,1).End(xlUp).Row会返回1，导致LastRow=2，循环For i=4 To LastRow根本不会执行，无法添加公式

修正后的代码 #

Option Explicit ' 强制声明所有变量，避免隐性错误

Sub CreateDataSheet()
    Dim dataSheet As Worksheet
    Dim lastRowMaterial As Long ' 记录Material表的有效数据行数
    Dim i As Long
    
    ' 先检查Data表是否已存在，避免重复创建报错
    On Error Resume Next
    Set dataSheet = ThisWorkbook.Sheets("Data")
    On Error GoTo 0
    
    ' 不存在则创建，且明确指定归属宏所在工作簿
    If dataSheet Is Nothing Then
        Set dataSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        dataSheet.Name = "Data"
    End If
    
    ' 写入列标题
    With dataSheet
        .Cells(3, 1).Value = "Component"
        .Cells(3, 2).Value = "Component Description"
        .Cells(3, 3).Value = "T18 Document Number"
        .Cells(3, 4).Value = "Module"
        .Cells(3, 5).Value = "Photo Status"
    End With
    
    ' 获取Material表的最后一行，确定需要填充的公式行数
    lastRowMaterial = ThisWorkbook.Sheets("Material").Cells(ThisWorkbook.Sheets("Material").Rows.Count, "G").End(xlUp).Row
    
    ' 动态填充对应行的公式
    With dataSheet
        For i = 4 To lastRowMaterial
            .Cells(i, 1).Formula = "=Material!G" & i ' 改为对应行的引用，而非固定G3
            .Cells(i, 2).Formula = "=Material!H" & i
            .Cells(i, 3).Formula = "=Material!J" & i
            .Cells(i, 4).Formula = "=Material!M" & i
            .Cells(i, 5).Formula = "=IF(C" & i & "=""Not Available"",""Not Available"",""Available"")"
        Next i
    End With
End Sub

关键修改说明 #

1. 锁定工作簿归属：创建工作表时用ThisWorkbook.Sheets.Add，确保新表属于宏所在的工作簿，彻底避免跨工作簿引用错误
2. 新增重复表检查：提前判断Data表是否存在，避免重复创建时抛出命名冲突错误
3. 修复公式逻辑：将固定引用Material!G3改为动态对应行的引用，同时根据Material表的实际行数确定填充范围
4. 强制变量声明：添加Option Explicit，杜绝因未声明变量导致的隐性bug

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