为什么我的array-merge打印除了索引中的最后一行之外的所有内容？

Question

我有一个脚本，它在文件夹路径中循环，将一组单个报告组合成一个主报告，然后它将整个编译后的数组集打印到一个主工作表上。它工作正常，在标题行之后打印，但它打印数组，直到最后编译的报告中的最后一行数据。数组索引中有148行，但它只打印其中的147行，然后退出sub。

这里是我做第二个数组的地方，一次打印它，也许这里的某个地方把它切掉了？

ReDim varArray2(1 To UBound(varArray, 2), 1 To UBound(varArray, 1))

With Workbooks("Retail_Goal_Report.xlsm").Worksheets("Merged Reports")

    For j = 1 To UBound(varArray, 2)
        For k = 1 To UBound(varArray, 1)
            varArray2(j, k) = varArray(k, j)
        Next
    Next

    .Range(.Cells(2, 1), .Cells(UBound(varArray, 2), UBound(varArray, 1))) = varArray2
End With

完整代码：

Option Explicit
Sub Sharepoint_Merge()


Dim k As Long, x As Long, j As Long ' counters
Dim varArray() As Variant
Dim varArray2() As Variant
ReDim varArray(1 To 23, 1 To 1)
Dim folderPath As String, filepath As String, filename As String
Dim wb As Workbook

Dim myvar As Variant
Dim myFolder As String
Dim i As Long

folderPath = "C:\merge\"

filepath = folderPath & "*.xlsx"

filename = Dir(filepath)

Call Ludicrous(True)

Do While filename <> ""

    Set wb = Workbooks.Open(folderPath & filename)

    With wb.Worksheets("Sheet1")

        For j = 2 To .UsedRange.Rows.Count + 1
            If .Cells(j, 1) <> "" Then
                x = x + 1
                ReDim Preserve varArray(1 To UBound(varArray, 1), 1 To x)
                    For k = 1 To UBound(varArray, 1)
                        varArray(k, x) = .Cells(j, k)
                    Next
            End If
        Next


    End With
    wb.Close
filename = Dir
Loop

    ReDim varArray2(1 To UBound(varArray, 2), 1 To UBound(varArray, 1))

    With Workbooks("Retail_Goal_Report.xlsm").Worksheets("Merged Reports")

        For j = 1 To UBound(varArray, 2)
            For k = 1 To UBound(varArray, 1)
                varArray2(j, k) = varArray(k, j)
            Next
        Next

        .Range(.Cells(2, 1), .Cells(UBound(varArray, 2), UBound(varArray, 1))) = varArray2
    End With

myFolder = "C:\Retail-Goal\merge\"
myvar = FileList(myFolder, "*.xlsx")
For i = LBound(myvar) To UBound(myvar)
    Kill myFolder & myvar(i)
Next

Call Ludicrous(False)


End Sub

Answer 1

在Nick Landa提交时，

.Range(.Cells(2, 1), .Cells(UBound(varArray, 2), UBound(varArray, 1))) = varArray2

应该是

.Range(.Cells(2, 1), 1 + .Cells(UBound(varArray, 2), UBound(varArray, 1))) = varArray2

你的数组是148行。如果您不选择148行范围，您将只输出所选内容，并主干所有其他内容。在本例中，它是147行，因为您从第2行开始，并且将向上移动到第148行。

附注:更好的方法是将迭代变量(而不是i和j)重命名为RowIterator和ColumnIterator，这样您就可以更好地了解正在发生的事情。

最后，为了优化代码的速度，而不是在专用步骤中转置矩阵，然后将值发送到工作表，您应该在专用指令中将值发送到工作表。

For RowIterator = 1 To UBound(varArray, 2)
    For ColumnIterator = 1 To UBound(varArray, 1)
        .Cells(1 + RowIterator, ColumnIterator) = varArray(ColumnIterator, RowIterator)
    Next
 Next

因为这种方法不涉及选择一个范围，然后复制其中的数组，所以您不会遇到这个问题。