来自范围的二维数组 [英] 2 Dimensional array from range

问题描述

我在 Excel 工作表中的单元格 B6:H14 中有文本数据.

I have text data in Excel worksheet in the cells B6:H14.

有些行有 2 个包含内容的单元格，而其他行有 4 个，有些则有 7 个.如何将这些复制到二维数组?我已经知道尺寸了，所以我对没有声明为动态代码的尺寸没问题.

Some rows will have 2 cells with contents while others have 4 and some will have 7. How do I copy these to a 2 dimensional array? I know the dimensions already and so, I am ok with the dimensions not being declared dynamic code.

我是否需要使用循环(我目前计划使用)?

Do I need to use a loop (which I am currently planning to use)?

或者有更简单/更优雅的方法吗?

Or is there an easier / more elegant way?

推荐答案

假设您的电子表格看起来像这样

Assuming your spreadsheet looks kind of like this

有一种非常简单的方法可以将其粘贴到二维数组中

There is a really easy way to stick that in a 2D array

Dim arr as Variant
arr = Range("B6:H14").Value

将此数组打印回电子表格的最简单方法

The easiest way to print this array back to spreadsheet

Sub PrintVariantArr()

    Dim arr As Variant
    arr = Range("B6:H14")

    Range("B16").Resize(UBound(arr, 1), UBound(arr, 2)) = arr

End Sub

或者你可以迭代/循环数组

Or you can iterate/loop the array

Sub RangeToArray()

    Dim arr As Variant
    arr = Range("B6:H14").Value
    Dim r As Long, c As Long

    r = 16
    c = 2

    Dim i, j
    For i = LBound(arr, 1) To UBound(arr, 1)
        For j = LBound(arr, 2) To UBound(arr, 2)
            Cells(r, c) = arr(i, j)
            c = c + 1
        Next j
        c = 2
        r = r + 1
    Next i

End Sub

并将您的数组打印回电子表格

And your array printed back to the spreadsheet

这篇关于来自范围的二维数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！