从打开的工作簿中转置单元格集合

Question

这是背景故事：我一直在享受 Excel 2007 中 VBA 的多功能性，但我在尝试从另一个工作簿中引用一系列单元格时遇到了困难。 更明确地说，我想使用 For Each Next 过程从 ThisWorkbook 中的单元格 I12 开始创建一个列式列表，该过程将从工作表的第一行(工作表变量为 CompShtName(中获取单元格值(数量不等(在 OldFile 工作簿中。 以下是我编写的代码摘录：

CompShtName = Range("E12").Value 
For Each Cell In OldFile.Sheets(CompShtName).Range("A1", Range("A1").End(xlRight))  **<---Run-time error 1004**  
    Range("I" & 12 + i).Value = Cell.Value  
    i = i + 1  
Next

我已经声明并定义了变量，其中：旧文件的OldFile = file地址，Old文件中工作表的CompShtName = name，以及i = counter。 当我尝试运行代码时，我在包含箭头的行中run-time error 1004。

我想也许问题出在从另一本书中提取。 但是如果我将范围更改为更简单的东西，例如 Range("A1:A5") ，它可以工作。 我什至尝试删除OldFile.Sheets(CompShtName).并让它使用活动ThisWorkbook运行，但这也可以。 所以问题一定出在试图创建一个笨拙的集合上。

---

更新

我真的很喜欢@Zemens使用换位而不是循环的方法。 我还确保使用@mehow的建议正确限定我的范围。 不幸的是，我仍然收到 1004 错误。 以下是更新后的代码：

Dim OldSht As Worksheet
Dim EndOldHeader As Range
Set OldSht = OldFile.Sheets(CompShtName) Set EndOldHeader = OldSht.Cells(1, OldSht.Cells(1, Columns.Count).End(xlToRight)) Compare.Range("I12").Resize(EndOldHeader.Columns.Count, 1).Value = Application.Transpose(EndOldHeader.Value)

错误发生在Set EndOldHeader行中。

Answer 1

假定

非限定 Range 对象Range("A1").End(xlRight)始终引用ActiveWorkbook 。 您需要根据@mehow的评论对其进行完全限定。

引发 1004 错误是因为无法构造跨多个工作簿的 Range 对象:)

我发现在使用这样的大型结构来创建对象变量来表示它们时会更好。它们将来更易于阅读、更新、维护和调试。

但是，您可以在没有循环结构的情况下执行此操作。它应该更快，尽管可能不会明显更快。

Dim oldSheet as Worksheet
Dim oldRange as Range
Dim cl as Range
Set oldSheet = OldFile.Sheets(CompShtName)
Set oldRange = oldSheet.Cells(1, oldSheet.Cells(1, Columns.Count).End(xlToRight))
ActiveSheet.Range("I12").Resize(oldRange.Columns.Count,1).Value = Application.Transpose(oldRange.Value)

或者，在循环中执行以下操作：

For each cl in oldRange.Cells
    ActiveSheet.Range("I" & 12 + i).Value = cl.Value  
    i = i + 1  
Next