如何将数组返回到Excel VBA范围 [英] How to return array to range excel vba

问题描述

我一直在尝试从工作表中获取数据并将其放入数组中，然后将数组粘贴到其他工作表中.但是，循环后，我的数组返回 Empty .我需要从For循环中返回一些东西吗?我搜索没有发现任何想法.

I have been trying to get data from a worksheet and put it into array and then paste the array to other worksheet. However, after the loop my array return Empty. Do I need to return something from the For Loop? I searched didn't find any idea.

Sub generate()
    Dim article_arr() As Variant
    Dim artCount As Integer
    Dim filter As Integer
    Dim RIL_itemCount As Integer

    'Set PA number
    filter = Sheet7.Range("B1").Value
    RIL_itemCount = Sheet5.Cells(Sheet5.Rows.count, "A").End(xlUp).Row

    'Count number article of PA selected
    artCount = Application.WorksheetFunction.CountIf(Sheet5.Range("R:R"), filter)

    'redim array
    ReDim article_arr(0 To artCount)
    Dim j As Integer
    j = 0

    'populate array with article number from Retail Item List
    For i = 0 To RIL_itemCount
        If (Sheet5.Cells(i + 2, 18).Value = filter) Then
            article_arr(j) = Sheet5.Cells(i + 2, 1).Value          
            Debug.Print (article_arr(j))
        End If
    Next

    'Paste Article number to range
    Sheet7.Range("A8:A" & artCount) = articleArr()


End Sub

正如David G所述.我忘了增加J.在粘贴数组时，我还使用了错误的变量(新手错误).现在，它返回结果，但只返回在粘贴范围内重复的数组的第一个值.我需要for循环才能将Array粘贴到范围内吗?

As mentioned by David G. I forgot to increment the J. I also use the wrong variable (newbie mistake) when pasting the Array. It now return result but it only return the first value of the array repeated over the pasted range. Do I need for loop to paste Array to range?

显然，数组将在Excel中水平粘贴，这会导致在将数组粘贴到范围时重复第一个值.添加WorksheetFunction.Transpose(array)神奇

Apparently array will be pasted horizontally in the excel, which cause repetition of the first value when pasting the array to range. Adding WorksheetFunction.Transpose(array) do the magic

这是更新的代码:

Sub generate()
    Dim article_arr() As Variant
    Dim artCount As Integer
    Dim filter As Integer
    Dim RIL_itemCount As Integer

    'Set PA number
    filter = Sheet7.Range("B1").Value
    RIL_itemCount = Sheet5.Cells(Sheet5.Rows.count, "A").End(xlUp).Row

    'Count number article of PA selected
    artCount = Application.WorksheetFunction.CountIf(Sheet5.Range("R:R"), filter)

    'redim array
    ReDim article_arr(0 To artCount)
    Dim j As Integer
    j = 0

    'populate array with article number from Retail Item List
    For i = 0 To RIL_itemCount
        If (Sheet5.Cells(i + 2, 18).Value = filter) Then
            article_arr(j) = Sheet5.Cells(i + 2, 1).Value
            j = j + 1
        End If
    Next

    'Paste Article number to range
    k = 8
    Sheet7.Range("A" & k & ":A" & UBound(article_arr) + 7) = WorksheetFunction.Transpose(article_arr)
    Debug.Print (article_arr(395))


End Sub

推荐答案

您的数组应该根据j整数填充，但您无需对其进行递增.

Your array is supposed to be filled according to the j integer but you don't increment it.

For i = 0 To RIL_itemCount
    If (Sheet5.Cells(i + 2, 18).Value = filter) Then
        article_arr(j) = Sheet5.Cells(i + 2, 1).Value
        j = j + 1
        Debug.Print (article_arr(j))
    End If
Next

另外，将数组粘贴到单个单元格时，它也将完全按照您的描述进行操作；将第一个数组值粘贴到各处以获取数组的大小.要使其具有正确的值，您需要将其发送到与数组大小相同的范围内.例如，对于2乘3的数组，您将编写

Also when pasting an array to a single cell, it will do exactly what you're describing; paste the first array value everywhere for the size of the array. To have it put the correct values, you need to send it to a range of the same size as the array. For example, for an array of size 2 by 3, you would write

Range("A1:B3") = array

在您的情况下，您希望大小是动态的，就像数组的大小一样.

In your case you would want the size to be dynamic, just like the size of your array.

k = 8
Range("A" & k & ":A" & k + Ubound(article_arr, 1)) = article_arr

应该做到这一点.如您所见，它将粘贴从A8开始并向下与数组中的值数相同长度的范围.

Should do the trick. As you can see it will paste the range starting at A8 and going down the same length as the number of values in the array.

这篇关于如何将数组返回到Excel VBA范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！