将数组复制到过滤的范围会产生不合理的结果 [英] Copying an array to a filtered range gives irrational results

问题描述

将过滤范围的值复制到数组似乎没有问题：数组然后包含来自过滤和未过滤的单元格的值。但是，当我将数组的内容复制到过滤范围时，结果对我来说是不可理解的。

这是我的代码：

$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $


$ b rangecopy（）=范围（单元格（2，1），单元格（14，3））值
对于c = LBound（rangecopy，1）到UBound（rangecopy，1）
rangecopy（c，1）= c
rangecopy（c，2） = c * 10
rangecopy（c，3）= c * 100
下一个
范围（单元格（2，1），单元格（14,3））Value = rangecopy（）
End Sub

应该给出以下结果。这里，当宏将数组复制到它时，范围是未过滤的。

如果范围被列D过滤（否被过滤掉），结果如下所示：

首先，过滤的单元格不会更新。然后，列B中的大多数单元格从数组的第一列（4,5,6）获取值，而另外几个数组从数组的第二列正确获取值（10）。最后两行填满＃N / A错误。这是应该这样工作吗？我正在使用Office 2010。

解决方案

我真的希望拥有VBA内部运作知识的人能够提供更多的洞察力题。我可以分享以下内容：

首先，它按照预期工作。但是，我不知道为什么这是设计，也不知道在分配过程中究竟发生了什么。

有很多情况造成类似的问题。例如，如果您有过滤器（某些行被隐藏）并尝试填充（拖动）公式，您将看到类似的结果，因为隐藏的行没有填充，但它们确实影响（相对）引用在公式。另一方面，如果您手动复制并粘贴到过滤的范围内，则将数据粘贴到隐藏的行（按照您的意图）。

似乎任何范围参考的是Autofilter范围的一部分实际上是非连续的*。使用Range.Address并不总是显示这个，也不会循环遍历Range.Areas。如果我们修改你的例子，我们可以看到真实错误在哪里：

  Dim r1 as range 
 Dim r2作为范围
 
设置r1 = Sheet1.Range（A1：B5）'范围内的一些数字
设置r2 = Sheet2.Range（A2：B6）'same-尺寸范围在一个过滤的标题下
 
 r1.Copy目的地：= r2 
  

当所有行都可见时，它将起作用。当Sheet2上的过滤器创建隐藏的行时，结果是运行时错误1004'：...复制区域和粘贴区域的大小和形状不同。另一方面，使用手动/剪贴板方法适用于隐藏的行：

  r1.Copy 
 r2 .PasteSpecial（xlPasteValues）
  

由于将一个数组分配给一个范围，所以绕过了剪贴板），我们应该收到一个错误（相反，你最终会出现错误的结果）。

我知道的唯一解决方案是循环遍历该范围，为每个单元格分配一个值：

 对于i = 1到LastRow 
对于j = 1到LastCol 
 Sheet1.Cells（i，j）.Value = myArr（i，j）
下一个
下一个
  

或（更好地）删除Autofilter，将数组分配给范围，然后重新应用过滤器。

*技术上它是连续的，所以它可能更好的说，该范围由几个范围/区域组成，尽管使用.Address并不表示这一点，只有一个区域，当您尝试循环通过Range.Areas

Copying the values of a filtered range to an array seems to work without a problem: the array then contains values from both filtered and unfiltered cells. However, when I copy the array's contents back to the filtered range, the results are incomprehensible to me.

Here's my code:

Sub test()
    Dim rangecopy() As Variant

    rangecopy() = Range(Cells(2, 1), Cells(14, 3)).Value
    For c = LBound(rangecopy, 1) To UBound(rangecopy, 1)
        rangecopy(c, 1) = c
        rangecopy(c, 2) = c * 10
        rangecopy(c, 3) = c * 100
    Next
    Range(Cells(2, 1), Cells(14, 3)).Value = rangecopy()
End Sub

It is supposed to give the following result. Here, the range was unfiltered when the macro copied the array to it.

If the range is filtered by column D ("NO" is filtered out), the result looks like this:

First, the filtered cells aren't updated. Then, most cells from column B get values from the array's first column (4, 5, 6), while a few others get values from the array's second column correctly (10). The last two rows are filled with #N/A error. Is this supposed to work that way? I'm using Office 2010.

解决方案

I really hope someone with knowledge of the internal workings of VBA can provide more insight on your question. I can share the following:

First, it is working as intended. However, I don't know why this is the design, nor what exactly is happening in the assignment process.

There are many cases that create a similar issue. For instance, if you have the filter on (some rows are hidden) and try to fill (drag) a formula down, you will see similar results, in that hidden rows aren't populated, but they do affect the (relative) references in the formula. On the other hand, if you manually copy and paste into a filtered range, the data is pasted into the hidden rows (as you intend).

It seems that any range referenced that is part of the Autofilter range is actually non-contiguous*. Using Range.Address does not always reveal this, nor does looping through Range.Areas. If we modify your example, we can see where the "real" error is:

Dim r1 as range
Dim r2 as range

Set r1 = Sheet1.Range("A1:B5") 'some numbers in a range
Set r2 = Sheet2.Range("A2:B6") 'same-size range underneath a filtered header

r1.Copy Destination:=r2

It works when all the rows are visible. When the filter on Sheet2 creates hidden rows, the result is "Run-time error '1004': ...the Copy area and the paste area are not the same size and shape." On the other hand, using the "manual" / clipboard method works for hidden rows:

r1.Copy    
r2.PasteSpecial (xlPasteValues)

Since assigning an array to a range bypasses the clipboard (as in the 1st block), we ought to receive an error (instead you just end up with erroneous results).

The only solutions I'm aware of are to either loop through the range and assign a value to each cell:

For i = 1 to LastRow
  For j = 1 to LastCol
    Sheet1.Cells(i,j).Value = myArr(i,j)
  Next
Next

OR (better) remove the Autofilter, assign the array to the range, then reapply the filter.

*technically it's contiguous, so it may be better to say that the range is composed of several ranges/areas, although using .Address doesn't indicate this and there is only one area when you try to loop through Range.Areas

这篇关于将数组复制到过滤的范围会产生不合理的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！