Array question

[remy988]

I am trying to understand how an array works. I have data in column C beginning on row 4. How do i read the data on Column C into an array and then use that data elsewhere in the macro?

this is all i have right now

Sub TEST()

    LastRow = Cells(Rows.Count, "c").End(xlUp).Row
    
    For i = 4 To LastRow
        dat = Range("c" & i)
    
    Next i

End Sub

thanks
rem

[snb]

sub M_snb()
  sn=columns(3).specialcells(2).offset(3).specialcells(2)
End Sub

[remy988]

hi snb,
what you posted is beyond my level of expertise. can you explain?

thanks
rem

[Blade Hunter]

Hi Remy.

An Array is a group of things called elements.

You can create an array in a similar way to what you are trying using a loop or you can populate an array in one go like so:

Sub ShowArrayBuilding()
Dim MyArray as variant '<-- need to do this to populate in one go
MyArray = Array("Hello","World","123")
end sub

This has now created an array for us with 3 elements, we can prove this by adding the following lines before end sub

For X = lbound(MyArray) to ubound(MyArray)
    msgbox(MyArray)
Next

We would also need to dim the variable X as a long up the top of the sub.

Now, an array can also be populated from a range like so:

Sub ShowArrayBuilding()
Dim MyArray As Variant '<-- need to do this to populate in one go
MyArray = Range("M2:M4")
End Sub

And you can also refer to the elements of an array as a collection using for each like so:

Sub ShowArrayBuilding()
Dim MyText As Variant, MyArray As Variant '<-- need to do this to populate in one go
MyArray = Range("M2:M4")
For Each MyText In MyArray
    MsgBox MyText
Next
End Sub

Last but by no means least, I think you should check out multi dimension arrays, these things are awesome.

You can have arrays instead of MyArray(1) MyAray(2) MyArray(3) etc you can have many dimension like MyArray(1,1) MyArray(1,2) MyArray(1,3) MyArray(2,1) MyArray(2,2) etc

You are not limited to 2 dimensions either, these things can get massive. So how would that be useful?

Consider you have a bunch of CD's

On each CD you have a bunch of tracks

You can refer to these tracks using a collective 2 dimension array.

Now expand your thoughts to a planet which has many oceans which has many ships which carry many shipping containers which have many boxes which contain many items

You are now at 6 dimension which the ability to pinpoint 1 individual item through simply knowing 6 location digits.

I hope this has helped you. Post back if you have more questions

[snb]

More on arrays:

http://www.snb-vba.eu/VBA_Arrays_en.html

[remy988]

thanks Blade,
that works great. If I had a text file that contained similar information, just 1 dimension. How would I code that?

snb,
thanks for your link.


rem

[Blade Hunter]

Well you could stream the text file to an array but I would think opening it in Excel, plopping it into and array then closing it again would be the easiest for you to understand at this point. Do you need an example?

Cheers

Dan

[remy988]

Dan,
thanks, I think I'm good for now.

[Paul_Hossler]

Another thought ---

Option Explicit
Sub TEST_1()
    Dim rStart As Range, rEnd As Range
    Dim vArray As Variant
    Dim i As Long
    
    
    Set rStart = ActiveSheet.Cells(1, 3).End(xlDown)
    Set rEnd = ActiveSheet.Cells(ActiveSheet.Rows.Count, 3).End(xlUp)
    
    ' since the WS is 2 dimentional so is vArray (1 to 9, 1 to 1)
    vArray = Range(rStart, rEnd).Value
    
    'so you need to handle it as a 2 D array = both indexes
    For i = LBound(vArray, 1) To UBound(vArray, 1)
        MsgBox "2D array -- " & i & " -- " & vArray(i, 1)
    Next i
    
    
    'But wait .... there's more
    
    'you most likely want a 'simple' 1 D array and you can get it like this
    vArray = Application.WorksheetFunction.Transpose(Range(rStart, rEnd).Value)
    
    For i = LBound(vArray) To UBound(vArray)
        MsgBox "1D array -- " & i & " -- " & vArray(i)
    Next i
End Sub