行列转置时Run-time error '1004'及代码循环报错问题咨询

Let’s break down why you’re hitting these errors in your second chunk of code, and fix them step by step.

Key Context from Your Problem #

You noted:

• Error 1004 triggers on the first For loop line of Chunk 2 (using variables í and k)
• Chunk 1 uses a logically identical loop but runs without issues
• You’ve moved dimension declarations and Set statements before the problematic loop, but still get Error 424 (Object Required) or 1004

Likely Root Causes & Practical Fixes #

1. The Accented Variable í Might Be Confusing the VBA Compiler #

VBA has inconsistent support for Unicode variable names, especially accented characters like í. Even if you declared it correctly, the compiler might misinterpret the variable in certain contexts (like cross-language environments or after copy-pasting code).

Fix: Replace í with a plain, non-accented variable name like i or rowIdx:

' Original Chunk 2 loop
For í = 1 To n
    For k = 1 To j
        ' Your transpose logic
    Next k
Next í

' Modified version
For i = 1 To n
    For k = 1 To j
        ' Your transpose logic
    Next k
Next i

2. Object References Aren’t Properly Initialized (Error 424) #

Even if you moved Set statements earlier, double-check that all worksheet/range objects are assigned valid references. For example:

• Ensure your target worksheets exist in the workbook, and add sanity checks to catch missing objects:

  Dim wsSource As Worksheet, wsTarget As Worksheet
  ' Use CodeName instead of sheet names if you want to avoid issues with renaming
  Set wsSource = ThisWorkbook.Worksheets("SourceData")
  Set wsTarget = ThisWorkbook.Worksheets("TransposedData")
  
  ' Catch missing sheets before running the loop
  If wsSource Is Nothing Or wsTarget Is Nothing Then
      MsgBox "One or more target worksheets are missing!", vbCritical
      Exit Sub
  End If
  

3. Loop Ranges Don’t Match Array/Cell Dimensions (Error 1004) #

Error 1004 often pops up when your loop tries to access an array index or cell that doesn’t exist.

Fix: Verify your loop bounds match your data dimensions before running the loop:

' If using an array, print its bounds to the Immediate Window (Ctrl+G to open)
Debug.Print "Source array bounds: " & LBound(sourceArr, 1) & " to " & UBound(sourceArr, 1)
Debug.Print "Loop bounds: n=" & n & ", j=" & j

' Ensure loop ranges stay within valid array limits
If n > UBound(sourceArr, 1) Or j > UBound(sourceArr, 2) Then
    MsgBox "Loop bounds exceed array dimensions!", vbExclamation
    Exit Sub
End If

4. Avoid Limitations of the Built-In Transpose Function #

If you’re using WorksheetFunction.Transpose, note it has hard limits on the number of rows/columns it can handle (older Excel versions cap at 65536 rows). This will trigger Error 1004 if your data exceeds those limits.

Better Fix: Use a manual array transpose—it’s more reliable and faster:

' Step 1: Read source data into an array
Dim sourceArr As Variant
sourceArr = wsSource.Range("A1").CurrentRegion.Value

' Step 2: Initialize transposed array (swap rows/columns)
Dim targetArr As Variant
ReDim targetArr(1 To UBound(sourceArr, 2), 1 To UBound(sourceArr, 1))

' Step 3: Transpose the data
For i = 1 To UBound(sourceArr, 1)
    For k = 1 To UBound(sourceArr, 2)
        targetArr(k, i) = sourceArr(i, k)
    Next k
Next i

' Step 4: Write transposed data to target sheet
wsTarget.Range("A1").Resize(UBound(targetArr, 1), UBound(targetArr, 2)).Value = targetArr

Quick Debugging Tips #

• Add a breakpoint (F9) on the problematic For loop line. When the code pauses, hover over variables like í, n, j, and your worksheet objects to check their values.
• Capture detailed error info to narrow things down:

  On Error Resume Next
  ' Your Chunk 2 loop code here
  If Err.Number <> 0 Then
      MsgBox "Error Details: " & Err.Number & " - " & Err.Description & vbCrLf & "Triggered at: For í = 1 To n"
      Err.Clear
  End If
  On Error GoTo 0
  

内容的提问来源于stack exchange，提问作者I. Я. Newb