excel vba如何将多个非连续范围的值复制到数组中 [英] excel vba how to copy the value of multiple non-contiguous ranges into an array

问题描述

我正在尝试将多个非连续范围的值复制到数组中。我写了这样的代码：

  summaryTempArray = .range（A2：D9，A11：D12，A14：D15）。价值
  

但它只复制第一部分（A2：D9）。然后，我尝试了以下内容，我收到错误 - 方法联盟的对象_全球失败 - 是否有任何错误，我使用联盟的方式？

  summaryTempArray = Union（.range（A2：D9），.range（A11：D12），.range（A14：D15））值
  




解决方案

不知道你的 union ，但它会创建相同的范围，您首次尝试说明。

问题是，现在有多个区域。你可以，据我所知，现在必须解决。

这是一个例子，它将解决所有领域的数组，而不添加每个单元格单独添加每一个区域到汇总数组：

  Public Sub demo（）
 Dim summaryTempArray（）As Variant 
 Dim i As Long 
 
 With Tabelle1 
 ReDim summaryTempArray（1 To .Range（A2：D9，A11：D12，A14：D15）。Areas.Count） 
 
对于i = 1到.Range（A2：D9，A11：D12，A14：D15）。Areas.Count 
 summaryTempArray（i）= .Range（A2：D9 ，A11：D12，A14：D15）。区域（i）
下一步i 
结束
 
 End Sub 
  pre> 
 
 
希望这有帮助。
 
I am trying to copy the value of multiple non-contiguous ranges into an array. I wrote code like this:
summaryTempArray = .range("A2:D9,A11:D12,A14:D15").Value
But it copies only the first part (A2:D9). Then, I tried the following and I get the error - "Method Union of Object _Global Failed" - is there any mistake in the way that I am using union?
summaryTempArray = Union(.range("A2:D9"), .range("A11:D12"), .range("A14:D15")).Value

解决方案
Don't know what was wrong with your union, but it would have created the same range, which you stated in your first attempt.

The problem is, you have now multiple areas. Which you can, and as far as I know, has to address now.

Here is an example, which will resolve in an array of all areas, without adding each cell individually, but adding each area individually to the summary array:
Public Sub demo()
  Dim summaryTempArray() As Variant
  Dim i As Long

  With Tabelle1
    ReDim summaryTempArray(1 To .Range("A2:D9,A11:D12,A14:D15").Areas.Count)

    For i = 1 To .Range("A2:D9,A11:D12,A14:D15").Areas.Count
      summaryTempArray(i) = .Range("A2:D9,A11:D12,A14:D15").Areas(i)
    Next i
  End With

End Sub
Hope this helps.

                        
这篇关于excel vba如何将多个非连续范围的值复制到数组中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！