CODEWITHSUNDEEP
arraysvbaexcel
oliverbj
oliverbj

Reputation: 6052

How can I add values/range to an array in a loop?

I have the below loop in 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)

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

Upvotes: 0

Views: 1878

Answers (3)

Vityata
Vityata

Reputation: 43585

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.

Upvotes: 1

tigeravatar
tigeravatar

Reputation: 26640

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

Upvotes: 1

Léo R.
Léo R.

Reputation: 2698

you don't need Loop

Just try this :

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

Upvotes: 1

Related Questions