VBA 矩阵打印

Question

下面的代码应该打印出生成的矩阵。我可以从"局部变量"窗口中看到，矩阵在 202 行和 4 列上保存值。

但是最后一行：

Worksheets("Sheet6").Range("C" & ExportToLine) = Matrix

仅打印出矩阵中的第一个值，如 (1,1( 所示。我做错了什么？我在这里尝试了大量不同的变量。

Sub MatrixPrint()
Dim lr1 As Long
Dim lr2 As Long
Dim x As Long
Dim arr As Range
Dim rng As Variant, cl As Range
Dim ws As Worksheet
Dim i As Long
Dim Matrix As Variant
With Sheet2
lr1 = Worksheets("ExampleOne").Cells(.Rows.Count, 1).End(xlUp).Row
Set arr = Worksheets("ExampleOne").Range("K2:K" & lr1 + 1)
End With
'Define cash receipt range from which to look from
With Blad16
lr2 = Worksheets("ExampleTwo").Cells(.Rows.Count, 6).End(xlUp).Row
rng = Worksheets("ExampleTwo").Range("F20:F" & lr2)
End With
x = 0
For i = LBound(rng) To UBound(rng)
Set C1 = arr.Find(rng(i, 1), LookIn:=xlValues)
If C1 Is Nothing Then
Else
x = x + 1
End If
Next i
Dim arr2 As Variant
Dim rng2 As Range
With Sheet2
lr1 = Worksheets("ExampleOne").Cells(.Rows.Count, 1).End(xlUp).Row
arr2 = Worksheets("ExampleOne").Range("A2:K" & lr1 + 1)
End With
With Blad16
lr2 = Worksheets("ExampleTwo").Cells(.Rows.Count, 6).End(xlUp).Row
Set rng2 = Worksheets("ExampleTwo").Range("F20:F" & lr2)
End With

ReDim Matrix(1 To UBound(arr2) - x, 1 To 4)
x = 1
For i = LBound(arr2) To UBound(arr2) - 1
Set c2 = rng2.Find(arr2(i, 11), LookIn:=xlValues)
If c2 Is Nothing Then
Matrix(x, 1) = arr2(i, 3)
Matrix(x, 2) = arr2(i, 4)
Matrix(x, 3) = arr2(i, 8)
Matrix(x, 4) = arr2(i, 11)
x = x + 1
Else
End If
Next i
Debug.Print (x)
Worksheets("Sheet6").Select
ExportToLine = 16 
Worksheets("Sheet6").Range("C" & ExportToLine) = Matrix
End Sub

Answer 1

Worksheets("Sheet6").Range("C" & ExportToLine)只返回一个单元格。Excel 将写入范围和数组的交集。所以如果你这样做了

Worksheets("Sheet6").Range("C" & ExportToLine).Resize(2,2).Value = Matrix

然后，无论矩阵有多大，您都会得到四个值。你想要的是

Worksheets("Sheet6").Range("C" & ExportToLine).Resize(UBound(Matrix,1), UBound(Matrix,2)).Value = Matrix