Excel VBA 1004错误排查及数组调整与转置技术问询

一、排查1004应用程序定义/对象定义错误 #

你提到的.Range(Cells(2, j + 1))确实是这类错误的高发点，大概率是工作表引用不一致导致的，而非语法问题。

核心问题：未明确限定Cells的归属 #

当你写.Range(Cells(2, j+1))时，Range是通过工作表对象（比如Sheets("Std. BOMs")）限定的，但Cells默认指向当前活动工作表。如果你的代码中Sheets("Std. BOMs")不是始终处于激活状态（比如中间切换了工作表，或者Select语句执行后被其他操作打断），就会出现“跨工作表引用”的冲突，触发1004错误。

修复方案：用With语句统一限定工作表 #

避免依赖Select（它不仅容易出错，还会降低代码效率），改用With块明确指定所有Range/Cells的归属：

Option Explicit
Sub Macro2()
    Dim i As Integer, j As Integer, lrow As Long, rng As Range, cell As Range, size As Integer
    Dim ws As Worksheet
    
    ' 先定义工作表对象，避免依赖Select
    Set ws = Sheets("Std. BOMs")
    
    With ws
        ' 所有Range/Cells前加.，表示归属ws工作表
        size = Application.WorksheetFunction.CountIf(.Range("A:A"), "你的条件") ' 补全你的CountIf参数
        ' 示例：正确引用Range(Cells)
        Set rng = .Range(.Cells(2, j + 1), .Cells(10, j + 1)) ' 这里的.Cells必须加.
    End With
End Sub

额外排查点 #

• 检查j的取值：确保j+1不会超出Excel的最大列数（Excel 2007+是16384列），如果j的循环范围没控制好，也会触发1004错误。
• 确认Range("...")的参数是否完整：你代码里的CountIf(Range("...")没写完，要补全条件参数，否则也会引发错误。

二、数组调整大小与转置的实现方法 #

1. 动态调整数组大小 #

VBA中动态数组需要用ReDim语句，若要保留原有数据，需搭配Preserve关键字：

• 一维数组：可以自由调整大小，Preserve会保留原有元素

  Dim arr() As Variant
  ReDim arr(1 To 5) ' 初始大小：5个元素
  arr(1) = "苹果": arr(2) = "香蕉"
  
  ReDim Preserve arr(1 To 10) ' 扩容到10个元素，原有数据保留
  arr(6) = "橙子"
  

• 二维数组：Preserve只能调整最后一维的大小。如果需要调整第一维，需先转置数组，调整后再转回来：

  Dim arr2D() As Variant
  ReDim arr2D(1 To 3, 1 To 2) ' 3行2列
  arr2D(1,1) = 1: arr2D(1,2) = 2
  arr2D(2,1) = 3: arr2D(2,2) = 4
  
  ' 要调整行数（第一维），先转置
  arr2D = Application.Transpose(arr2D) ' 转置后变成2行3列
  ReDim Preserve arr2D(1 To 2, 1 To 5) ' 调整列数（对应原数组的行数）
  arr2D = Application.Transpose(arr2D) ' 再次转置，恢复原结构：5行2列
  

2. 数组转置 #

直接使用Application.Transpose函数即可实现二维数组的行列互换：

' 从工作表读取二维数组
Dim sourceArr As Variant
sourceArr = Sheets("Sheet1").Range("A1:C3").Value ' 3行3列的数组

' 转置数组
Dim transposedArr As Variant
transposedArr = Application.Transpose(sourceArr) ' 转置后行列互换，原3行3列仍为3行3列，若原是2行3列则变为3行2列

' 将转置后的数组写回工作表
Sheets("Sheet1").Range("E1:G3").Value = transposedArr

⚠️ 注意：Transpose有元素数量限制（不能超过Excel单行列的最大数，即1048576行或16384列），且可能会自动转换数据类型（比如长数字变成文本），使用时需留意。

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