使用Application.Transpose转置大数组后出现#N/A错误的求助

问题根源 #

Application.Transpose函数在处理元素数量较大的一维字符串数组时，存在内部限制（内存或参数长度限制），导致转置后的数组包含错误值，赋值到工作表单元格后显示#N/A。

无循环解决方案 #

方案一：利用Excel复制粘贴转置功能

通过临时行区域中转，借助Excel的粘贴转置操作完成数组维度转换，避开Application.Transpose的限制：

Dim a_ComboLovs() As String
Dim a_metasheet As Worksheet
Dim g_ComboSourceColCount As Long
Dim tempRowRange As Range
Dim targetColRange As Range

' 示例赋值
a_ComboLovs = Split("Value1,Value2,Value3,...,Value80000", ",")
g_ComboSourceColCount = 25

' 定义临时行区域和目标列区域
Set tempRowRange = a_metasheet.Cells(1, 1).Resize(1, UBound(a_ComboLovs) + 1)
Set targetColRange = a_metasheet.Cells(100, g_ComboSourceColCount).Resize(UBound(a_ComboLovs) + 1, 1)

' 将一维数组写入临时行区域
tempRowRange.Value = a_ComboLovs
' 复制并转置粘贴到目标列区域
tempRowRange.Copy
targetColRange.PasteSpecial Paste:=xlPasteValues, Transpose:=True
' 清除剪贴板状态
Application.CutCopyMode = False
' 可选：清除临时区域内容
tempRowRange.ClearContents

' 命名目标区域
targetColRange.Name = "Evalranage"

方案二：使用Application.Index构造二维列数组

通过Application.Index函数直接将一维数组转换为适合单列区域的二维数组，无需循环：

Dim a_ComboLovs() As String
Dim a_metasheet As Worksheet
Dim g_ComboSourceColCount As Long
Dim targetColRange As Range

' 示例赋值
a_ComboLovs = Split("Value1,Value2,Value3,...,Value80000", ",")
g_ComboSourceColCount = 25

' 定义目标列区域
Set targetColRange = a_metasheet.Cells(100, g_ComboSourceColCount).Resize(UBound(a_ComboLovs) + 1, 1)

' 转换数组维度并赋值
targetColRange.Value = Application.Index(a_ComboLovs, Evaluate("ROW(1:" & UBound(a_ComboLovs) + 1 & ")"), 1)

' 命名目标区域
targetColRange.Name = "Evalranage"

额外注意事项 #

• 原代码中通过字符串拼接定义区域的方式存在隐患：当列数超过26时（如27），g_ComboSourceColCount & "100"会生成"27100"，Excel会错误识别为列27100，而非AA列第100行。建议始终使用Cells+Resize的方式定义区域，避免此类错误。
• 若数组元素数量超过10万，方案一的复制粘贴转置更稳妥，方案二中的Evaluate可能触发长度限制。

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