从不同工作表复制范围并将其存储到数组中的最佳方法 [英] Best way to copy ranges from different sheets and store them into an array

问题描述

我正在尝试从不同的工作表中复制范围并将它们存储在数组中.什么是最好的方法 ?我知道UNION不能在不同的工作表上工作，所以这就是我正在做的事情，它正在工作，但是我想知道是否有更好的方法.预先感谢

I am trying to copy ranges from different sheets and store them in an array. what is the best way ? I know that UNION doesn't work from different sheets so here is what I am doing, it's working but I would like to know if there is a better way. thanks in advance

    Dim MyArray As Variant

Worksheets("Sheet1").Range("A1:A" & Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row).Copy Worksheets("main").Range("A1")
Worksheets("Sheet2").Range("B1:B" & Worksheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row).Copy Worksheets("main").Range("A11")
Worksheets("Sheet3").Range("C1:C" & Worksheets("Sheet3").Cells(Rows.Count, 3).End(xlUp).Row).Copy Worksheets("main").Range("A15")

    MyArray = Worksheets("main").Range("A1:A20").Value

推荐答案

您似乎误解了PEH的意图.我正在对此进行解释和扩展.这是他建议您缓存的工作表的图片.

It looks like you misunderstood PEH's intention. I am explaining and expanding on it. Here is a pictue of the worksheet he recommended you to cache.

您要缓存范围B2:D7(或将来的E7).这是执行缓存的代码.

You want to cache the range B2:D7 (or E7 in the future). Here is the code that does the caching.

Sub GetTemplateAddresses(AddList As Variant)
    ' loads AddList only if not already loaded

    Dim Rng As Range                ' Range of cached addresses table
    Dim Rl As Long                  ' last used row in Ws "Cache"

    If IsEmpty(AddList) Then                    ' skip if AddList is already set
        With Worksheets("Cache")                ' use your own name for this worksheet
            Rl = .Cells(.Rows.Count, "A").End(xlUp).Row
            ' start from B2 to (LastRow of Template3)
            Set Rng = .Range(.Cells(2, 2), .Cells(Rl, Ncc3 + 1))
            AddList = Rng.Value
        End With
    End If
End Sub

在调用此子程序之前，需要做一些准备.观察该子项具有参数 AddList .这个想法(由@PEH提供)是，如果它已经被缓存，则不需要再次对其进行缓存.因此，您提供了数组变量，并且只有在该变量为空的情况下，代码才会加载它.

Before you can call this sub you need to prepare a little. Observe that the sub has a parameter, AddList. The idea (courtesy @PEH) is that you don't need to cache it again if it's already cached. So, you supply the array variable and the code loads it only if it is blank.

另一种准备是上面代码中对枚举 Ncc3 的引用.枚举旨在简化整个AddList数组的寻址.请注意，在该工作表上的任何步骤之前，它必须位于任何标准代码模块的顶部.这是完整的枚举.显式选项

The other preparation is the reference in the above code to enumeration Ncc3. The Enum is designed to enable easy addressing of the entire AddList array. Mind that it must be at the top of any standard code module, before any procedure on that sheet. Here is the full enumeration. Option Explicit

Enum Ncc                            ' Array of Cached addresses
    Ncc1 = 1                        ' Template 1
    Ncc2
    Ncc3
    NccFname = 1                    ' assign integers only
    NccMname                        ' if no value is assigned,
    NccLname                        ' VBA assigns previous + 1
    NccCity
    NccStreet
    NccZip
    NccTel
    NccEmail                        ' NccEmail = 8 (test with ? NccEmail in the Immediate window)
End Enum

我为您设计了一些测试程序.将其安装在标准代码模块中，为什么不在上面的枚举和其调用的子代码下方?

I have designed a little testing procedure for you. Install it in a standard code module, why not below the above enumeration and above the sub it calls?

Private Sub TestGetAdd()
    Dim AddList As Variant
    Dim TemplateNumber As Ncc, FieldName As Ncc

    GetTemplateAddresses AddList
    TemplateNumber = Ncc2                   ' change the numbers for testing
    FieldName = NccStreet                   ' the press F5
    MsgBox "You have entered template No. " & TemplateNumber & vbCr & _
           "and specified field No. " & FieldName & String(2, vbCr) & _
           "The address is " & AddList(FieldName, TemplateNumber)
End Sub

已准备好运行.测试一下.

It is ready to run. Test it.

要使用整个设置，您现在可以在知道工作表的名称及其模板编号之后，继续从任何模板定义单元格.Ws.Range(AddList(NccCity，Ncc2)).Value将根据缓存中存储的字段的地址，从模板2返回城市名称.

To use this whole setup you can now proceed to define cells from any of your templates after you know the worksheet's name and its template number. Ws.Range(AddList(NccCity, Ncc2)).Value will return the name of the city from template 2 according to the address for that field stored in the cache.

这篇关于从不同工作表复制范围并将其存储到数组中的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！