将执行时定义的一系列单元格复制到数组中 [英] Copy a range of cells, defined on execution, to an array

问题描述

我正在尝试从两个工作簿的行范围中复制一定范围的单元格。此信息用于按ID比较两个工作簿行的内容。

I am trying to copy a range of cells from a range of rows from two workbooks. This information is used to do a comparison of the contents of both workbooks rows by ID.

我尝试的第一个解决方案涉及逐个单元的二进制比较。这适用于几行工作表：

The first solution I tried involved cell by cell "binary" comparison. This works for worksheets with few rows:

For i = 2 To LastSheetRow
    Set FoundCell = Workbooks(WorkbookA).Sheets(SheetNameFromArray).Range("A:A").Find(What:=Workbooks(WorkbookB).Sheets(SheetNameFromArray).Cells(i, 1).Value)
    If Not FoundCell Is Nothing Then

        aCellValues(0) = 1
        Workbooks(UserWorkbook).Sheets(SheetNameFromArray).Cells(i, LastSheetColumn + 1).Value = FoundCell.Row
        For j = 2 To LastSheetColumn
            Select Case Workbooks(WorkbookB).Sheets(SheetNameFromArray).Cells(i, j).Value
            Case Is = Workbooks(WorkbookA).Sheets(SheetNameFromArray).Cells(FoundCell.Row, j).Value
                aCellValues(j - 1) = 1

            Case Else
                aCellValues(j - 1) = 0
            End Select
        Next j
    Else
    End If
Next i

我想存储内容每排一排在一个数组上的两个工作簿进行比较，因为我认为这是一个更快的解决方案。

I would like to store the contents of one row of each of the two workbooks on one array to do the comparison, as I believe it's a faster solution.

在定义了要进行比较的范围之后，在复制数组时遇到了以下错误单元格放入数组：

After defining the range to do the comparison I encountered the following error when copying the cells into an array:

子索引超出间隔（错误9）

Subindex out of interval (Error 9)

这会产生错误：

Dim aWorkbookBInfo() As Variant, aWorkbookAInfo() As Variant, rngWorkbookBToCompare As Range, rngWorkbookAToCompare As Range
Dim SumToCheck As Integer, FoundCell As Range, aCellValues() As Integer   
ReDim aCellValues(LastSheetColumn - 1)
ReDim aWorkbookBInfo(LastSheetColumn - 1)
ReDim aWorkbookAInfo(LastSheetColumn - 1)
For i = 2 To LastSheetRow
    Set FoundCell = Workbooks(WorkbookA).Sheets(SheetNameFromArray).Range("A:A").Find(What:=Workbooks(WorkbookB).Sheets(SheetNameFromArray).Cells(i, 1).Value)
    If Not FoundCell Is Nothing Then        
        aCellValues(0) = 1
        Workbooks(WorkbookB).Sheets(SheetNameFromArray).Cells(i, LastSheetColumn + 1).Value = FoundCell.Row
        With Workbooks(WorkbookB).Sheets(SheetNameFromArray)
            Set rngWorkbookBToCompare = Range(Cells(i, 2), Cells(i, LastSheetColumn))
        End With
        With Workbooks(WorkbookA).Sheets(SheetNameFromArray)
            Set rngWorkbookAToCompare = Range(Cells(FoundCell.Row, 2), Cells(FoundCell.Row, LastSheetColumn))
        End With
        aWorkbookBInfo = rngWorkbookBToCompare
        aWorkbookAInfo = rngWorkbookAToCompare
        For j = 1 To LastSheetColumn - 1
            If aWorkbookBInfo(j).Value = aWorkbookAInfo(j).Value Then                
                aCellValues(j) = 1                
            Else
                aCellValues(j) = 0
            End If
        Next j
    Else
    End If
Next i

推荐答案

完整修订版：

范围数组分配在这些行中生成一个二维数组：

The range array assignment produces a two-dimensional array in these lines:

aWorkbookBInfo = rngWorkbookBToCompare
aWorkbookAInfo = rngWorkbookAToCompare

无论您在代码开始时如何定义和定义它们的大小，都会发生这种情况。由于它们是二维数组，因此必须以 aWorkbookBInfo（a，b）寻址，其中 a 是a行和 b 是列。

This happens regardless of how you defined and dimensioned them at the beginning of your code. Since they are a two-dimensional array, they must be addressed as aWorkbookBInfo(a, b) where a is a row and b is a column.

与范围不同，可以引用任何范围中的第一个单元格，您必须先完全寻址数组，然后再尝试引用该数组项。因此，虽然 rngWorkbookBToCompare（j）.Value 有效，但 aWorkbookBInfo（j）.Value 无效。此外， Value 不一定是Excel放入数组中的任何对象的属性。如果要使用列 j 的第一个单元格，请尝试添加该行，并保留对 Value 属性的引用，如下所示： aWorkbookBInfo（1，j）。

Unlike Ranges, where it is okay to reference the first cell in any range, you must fully address an array before attempting to reference the array item. So, while rngWorkbookBToCompare(j).Value works, aWorkbookBInfo(j).Value does not. Furthermore, Value is not necessarily a property of whatever object Excel puts in the array. If you want the first cell of column j, try adding the row and leaving off the reference to the Value property as in: aWorkbookBInfo(1, j).

这篇关于将执行时定义的一系列单元格复制到数组中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！