应用过滤器后Excel填充单元格范围

I want to apply a filter on a set of data, after that I would like to fill a range of data into a range of cells. But now if this will be done, the last two rows are getting the values of the first cell.

我想对一组数据应用过滤器,之后我想将一系列数据填充到一系列单元格中。但是现在如果这样做,最后两行将获得第一个单元格的值。

-74.4398
-74.2028
-69.8689
-73.1567
-80.1015
-75.822
-75.0529
-75.9859
-79.2546
-72.8093
-71.6604

-74.4398 -74.2028 -69.8689 -73.1567 -80.1015 -75.822 -75.0529 -75.9859 -79.2546 -72.8093 -71.6604

This is the list of numbers in cell B3 to B13. One button with the following VBA code behind:

这是单元格B3到B13中的数字列表。一个按钮后面有以下VBA代码:

Private Sub CommandButton1_Click()

    Dim arr() As Variant
    Dim i As Integer

    ReDim arr(1 To 11, 1 To 1)

    arr(1, 1) = "Hallo"
    arr(2, 1) = "Welt"
    arr(3, 1) = "Holla"
    arr(4, 1) = "verdugón"
    arr(5, 1) = "Hello"
    arr(6, 1) = "World"
    arr(7, 1) = "Ciao"
    arr(8, 1) = "mondo"
    arr(9, 1) = "Salut"
    arr(10, 1) = "terre"
    arr(11, 1) = "Final"

    Worksheets("Sheet1").Range("C3:C13").Value = arr

End Sub

If you now set a filter on cell C2 to just show all values greater than -75 you will get a list like that:

如果您现在在单元格C2上设置一个过滤器,只显示大于-75的所有值,您将获得如下列表:

-74.4398
-74.2028
-69.8689
-73.1567
-72.8093
-71.6604

-74.4398 -74.2028 -69.8689 -73.1567 -72.8093 -71.6604

If you now press the button so that VBA code will be executed, you will see that the content in cell C12 and C13 has the same value as in cell C3.

如果现在按下按钮以便执行VBA代码,您将看到单元格C12和C13中的内容具有与单元格C3中相同的值。

Why does it happened, why does it not fill up the cells with the right content ?

为什么会发生这种情况,为什么它没有用正确的内容填充单元格?

I expected that cell C12 will be 'terre' and C13 'Final'.

我预计细胞C12将是“terre”而C13将是“Final”。

Is that an excel issue / bug ?

这是一个excel问题/错误吗?

EDIT: It is more worst as I thought, I used now a different value for the filter -74 and not only the last entries are incorrect, all are incorrect:

编辑:我认为这是更糟糕的,我现在使用了不同的过滤器值-74,不仅最后的条目不正确,都是不正确的:

2 个解决方案

#1

I believe it is really a bug and it seems it has been there for some time - see this LINK.

我相信它确实是一个错误,它似乎已经存在了一段时间 - 看到这个链接。

#2

At first glance I would say it's not a bug, and it's moreso the nature of arrays, however I could be completely wrong. If you were looking for a working solution, I went ahead and created this:

乍一看,我会说它不是一个bug,而且它更像是数组的本质,但是我可能完全错了。如果您正在寻找可行的解决方案,我继续创建:

Sub Button2_Click()
Dim sht As Worksheet, lastrow As Integer, arr() As Variant
Set sht = ThisWorkbook.Worksheets("Sheet1")
lastrow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row

arr = Array("Hallo", "Welt", "Holla", "verdugón", "Hello", "World", "Ciao", "mondo", "Salut", "terre", "Final")

For i = 3 To lastrow
    Range("C" & i).Value = arr(i - 3)
Next i
End Sub