如何在循环中向数组添加值/范围？

I have the below loop in VBA:

我在VBA中有以下循环:

For i = 1 To Range("B" & "65536").End(xlUp).Row Step 1

        Companies = Range("A" & i).Value
Next i

MsgBox Companies 'Output Company Name (One time)

So above loop iterates through rows, that all have a company name in Column "A". I want to add all these company names to an array, so I can print them all out later on (after the loop)

因此,上面的循环遍历行,所有行都在列“A”中具有公司名称。我想将所有这些公司名称添加到一个数组中,所以我可以稍后将它们打印出来(在循环之后)

How can I dynamically add the Companies value to an array, and use it later on?

如何将公司值动态添加到数组中,并在以后使用它?

3 个解决方案

#1

you don't need Loop

你不需要循环

Just try this :

试试这个:

Dim DirArray As Variant
DirArray = Range("A1:A5000").Value

#2

I think something like this is what you're looking for.

我觉得这样的东西就是你要找的东西。

Sub tgr()

    'Declare variables
    Dim ws As Worksheet
    Dim Companies As Variant
    Dim i As Long

    'Always fully qualify which workbook and worksheet you're looking at
    Set ws = ActiveWorkbook.ActiveSheet

    'You can assing a Variant variable to the value of a range
    '  and it will populate the variable as an array if there
    '  is more than one cell in the range
    'Note that I am going off of column B as shown in your original code,
    '  and then using Offset(, -1) to get the values of column A
    Companies = ws.Range("B1", ws.Cells(ws.Rows.Count, "B").End(xlUp)).Offset(, -1).Value

    If IsArray(Companies) Then
        'More than one company found, loop through them
        For i = LBound(Companies, 1) To UBound(Companies, 1)
            MsgBox "Company " & i & ":" & Chr(10) & _
                   Companies(i, 1)
        Next i
    Else
        'Only one company found
        MsgBox Companies
    End If

End Sub

#3

If you need an array, which is increased every time and still saves its contents, something like this should work:

如果你需要一个每次都增加但仍保存其内容的数组,这样的东西应该有效:

Option Explicit
Public Sub TestMe()        
    Dim i           As Long
    Dim companies() As Variant        
    ReDim companies(0)        
    For i = 1 To 20
        ReDim Preserve companies(UBound(companies) + 1)
        companies(UBound(companies)) = Range("A" & i)
    Next i        
End Sub

If you need simply need to take the values to array, then the answer of @Leo R. is probably the easiest way to achieve it.

如果你只需要将值带到数组,那么@Leo R.的答案可能是实现它的最简单方法。

#1