时间戳

首页 > 解决方案 > 将 2 个范围组合成一个数组并过滤

arrays - 将 2 个范围组合成一个数组并过滤

问题描述

我有 2 个范围，都具有相同的行数，不同的列数（可能会有所不同）。

范围 1：

A,   1,   1,   1
B,   2,   4,   8
C,   3,   9,  27
D,   4,  16,  64

范围 2：

1,       1
16,     64   
81,    
256,   1024

我想将这些值导入 Excel 中的多数组，但前提是范围 2 的第 n 列（在本例中为第 2 列）不是空值。那么我最终会得到一个如下所示的数组：

输出 1：

A,   1,   1,   1,    1,     1
B,   2,   4,   8,   16,    32   
D,   4,  16,  64,  256,  1024

到目前为止 - 我已经开始了一个功能：

Function Report(rng1 As Range, rng2 As Range)
Dim matrix() As Double
Dim all_rng As Range
    all_rng = Union(rng1, rng2)

End Function

标签： arraysexcelvbafilter

解决方案

这是可能的决定。
注释：
1.为了测试方便，我把Functionto改为了Sub，因为我不能一步一步完成函数
2.有几行用于测试目的（在注释中标记）
3.我假设要填充的列数正确inrng2在它的第一行
4。有带有.Select语句的注释行 - 取消注释它，按 F8 逐步进行，您将看到它是如何工作的。

答案。

我把你的矩阵是这样的：
矩阵

从第 10 行开始有这样的输出：
输出

这是代码： 

Sub Report() 'rng1 As Range, rng2 As Range)
Dim matrix() As Variant ' use variant if you have a mix of letters and numbers
Dim x As Long, y As Long
Dim r As Long, c As Long
Dim rows() As Long, i As Long, rowCnt As Long


' used for test purposes
Dim rng1 As Range, rng2 As Range
Set rng1 = Range(Cells(1, 1), Cells(4, 4))
Set rng2 = Range(Cells(1, 9), Cells(4, 10))


' find out columns count per each range's row 1
x = Range(rng1.Cells(1, 1), rng1.Cells(1, rng1.Columns.Count)).Columns.Count

' I assume that the correct number of columns in rng2 is in the first row
' you may change the row number if needed
y = Range(rng2.Cells(1, 1), rng2.Cells(1, rng2.Columns.Count)).Columns.Count

' check that all rows have all columns filled
For i = 0 To rng1.rows.Count - 1
    ' if all columns in rng2 are filled then add number of the row to an array of row numbers
    If Not rng2.Cells(i + 1, y) = "    " Then ' fix evaluation condition if needed - that is what was copied from post
        ReDim Preserve rows(rowCnt)
        rows(rowCnt) = i + 1
        rowCnt = rowCnt + 1
    End If
Next

i = UBound(rows) - 1

' set dimension of an matrix array
ReDim matrix(rows(i), x + y)

' start filling the matrix

' go through matrix by row
For r = LBound(rows) To UBound(rows)
        ' fill each row column by column

        'gothrough first range - it has x columns in it
        For c = 0 To x - 1
'        rng1.Cells(rows(r), c + 1).Select
            matrix(r, c) = rng1.Cells(rows(r), c + 1).Value
        Next

        ' then without changing matrix's row
        ' go through second range - it has y columns
        For c = 0 To y - 1
'        rng2.Cells(rows(r), c + 1).Select
            matrix(r, c + rows(UBound(rows))) = rng2.Cells(rows(r), c + 1).Value
        Next
Next

' print the matrix to the sheet (optional - delete when convert this back to Function)
For r = LBound(matrix) To UBound(matrix)
    For c = 0 To x + y - 1
        Cells(10 + r, c + 1) = matrix(r, c)
    Next
Next
End Sub

如果您有任何问题 - 将它们放在评论中。

推荐阅读