英文:
Range.Consolidate in Excel: "Run-time error '1004': Application-defined or object defined error"
问题
我遇到了问题，不知何故，以至于我的头发都变白了。在“Test”工作表上有一些数据，我正在尝试汇总到“finaldata”工作表上。这是数据的屏幕截图：
Sub ConsolidateData()
Dim finalDataSheet As Worksheet
Dim testSheet As Worksheet
Dim finalDataRange As Range
Dim testRange As Range
' 设置工作表的引用
Set finalDataSheet = ThisWorkbook.Worksheets("finaldata")
Set testSheet = ThisWorkbook.Worksheets("Test")
' 设置要在finaldata工作表中汇总的范围
Set finalDataRange = finalDataSheet.Range("A1")
' 设置要从Test工作表中汇总的范围
Set testRange = testSheet.Range("A:P")
' 汇总数据
finalDataRange.Consolidate Sources:=Array(testRange.Address), Function:=xlSum, LeftColumn:=True
End Sub
我发现问题出在Sources:=Array(testRange.Address)这一部分，但我真的无法弄清楚是什么问题。
希望有人能帮助并了解我做错了什么。（我尝试问妻子，但她只是给我提供了一连串与问题无关的问题）。
谢谢，
马克
英文:
I am having issues getting this to work for some reason, to the extent my hair is turning grey.
There is some data on sheet 'Test' that I am trying to consolidate on sheet 'finaldata'. Here's a screenshot of data:
Sub ConsolidateData()
Dim finalDataSheet As Worksheet
Dim testSheet As Worksheet
Dim finalDataRange As Range
Dim testRange As Range
' Set references to the worksheets
Set finalDataSheet = ThisWorkbook.Worksheets("finaldata")
Set testSheet = ThisWorkbook.Worksheets("Test")
' Set the range to consolidate in the finaldata sheet
Set finalDataRange = finalDataSheet.Range("A1")
' Set the range to consolidate from the Test sheet
Set testRange = testSheet.Range("A:P")
' Consolidate the data
finalDataRange.Consolidate Sources:=Array(testRange.Address), Function:=xlSum, LeftColumn:=True
End Sub
I figured out the problem is in the Sources:=Array(testRange.Address) bit, but I can't really figure out what it is.
Hope anyone can help and has an inkling of what I am doing wrong. (I tried asking the wife, but all I got was an endless stream of non-relevant issues).
Thank you,
Mark
答案1
得分: 0
文档中写道：
将合并的来源作为以 R1C1 样式表示的文本引用字符串数组。 引用必须包括要合并的工作表的完整路径。
尝试这样做（未经测试）：
Sub 合并数据()
Dim 最终数据工作表 As Worksheet
Dim 测试工作表 As Worksheet
Dim 最终数据范围 As Range
Dim 测试范围 As Range
' 设置对工作表的引用
Set 最终数据工作表 = ThisWorkbook.Worksheets("finaldata")
Set 测试工作表 = ThisWorkbook.Worksheets("Test")
' 在最终数据工作表中设置要合并的范围
Set 最终数据范围 = 最终数据工作表.Range("A1")
' 在测试工作表中设置要合并的范围
Set 测试范围 = 测试工作表.Range("A:P")
' 合并数据
最终数据范围.Consolidate _
Sources:=Array(测试工作表.Name & "!" & 测试范围.Address(, , xlR1C1), _
Function:=xlSum, _
LeftColumn:=True
End Sub
英文:
The docs state:
> The sources of the consolidation as an array of text reference strings in R1C1-style notation. The references must include the full path of sheets to be consolidated.
Try this (not tested):
Sub ConsolidateData()
Dim finalDataSheet As Worksheet
Dim testSheet As Worksheet
Dim finalDataRange As Range
Dim testRange As Range
' Set references to the worksheets
Set finalDataSheet = ThisWorkbook.Worksheets("finaldata")
Set testSheet = ThisWorkbook.Worksheets("Test")
' Set the range to consolidate in the finaldata sheet
Set finalDataRange = finalDataSheet.Range("A1")
' Set the range to consolidate from the Test sheet
Set testRange = testSheet.Range("A:P")
' Consolidate the data
finalDataRange.Consolidate _
Sources:=Array(testSheet.Name & "!" & testRange.Address(, , xlR1C1), _
Function:=xlSum, _
LeftColumn:=True
End Sub
答案2
得分: 0
"The sources of the consolidation as an array of text reference strings in R1C1-style notation. The references must include the full path of sheets to be consolidated."
...
finalDataRange.Consolidate Sources:=Array(testSheet.Name & "!" & testRange.Address(0, 0, xlR1C1, False, testSheet.Range("A1"))), Function:=xlSum, LeftColumn:=True
...
英文:
"The sources of the consolidation as an array of text reference strings in R1C1-style notation. The references must include the full path of sheets to be consolidated."
....
finalDataRange.Consolidate Sources:=Array(testSheet.Name & "!" & testRange.Address(0, 0, xlR1C1, False, testSheet.Range("A1"))), Function:=xlSum, LeftColumn:=True
....
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论