Read Values into an Array 2

[MattGreer]

I'm smacking my forehead because this should be easy but I'm unsure about how to correctly use the RANGE object.

I have a column of values, always starting at cell "E8". The number of values in the column may change. Here's the code I have so far:

Option Explicit
Option Base 1

Private Sub ReadInArray()
Dim i, y, x As Long
Dim c As Range

x = 0
i = 1

'Count the rows in the array

x = Range("E" & Rows.Count).End(xlUp).Row - Range("E8").Row

ReDim aLevels(x)

For Each c In Range("E8", Range("E8").End(xlDown))
    aLevels(i) = Range("E" & 'And here I'm stuck
    i = i + 1
Next
End Sub

How do I specify to start at cell E8, stuff that value into the array, and then move down and repeat?

Thanks for your help!!

Thanks!!


Matt

 

[MattGreer]

I'm thinking the missing line is:

aLevels(i) = c.value

But I'm getting a 'subscript out of range' on aLevels(i).

/confused

Thanks!!


Matt

 

[MattGreer]

OK, seems like the value if i is going one more than it should... Hmmmm...

Thanks!!


Matt

 

[MattGreer]

Well, not sure why the row count calculation gives me one less, I figure it's probably something to do with being inclusive. So I just added a "+1" at the end of it and problem solved. Bleh.

Thanks!!


Matt

 

[TonyJollans]

How is aLevels originally declared?

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[GlennUK]

An easy way to get a range into an array:

    Dim bLevels() As Variant
    bLevels = Range("E8", Range("E8").End(xlDown)).Value

    ' you get an array, 2d, being bLevels(x,y),
    ' where x is number of rows in range, and y is number of columns, base of 1

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[MattGreer]

Tony,

The array was not previously declared.


GlennUK you are a MASTER! Sweet! Thanks!!

Actually, I am working with a 2d array, aLevels(2,X). The values I'm working with are timed values, so the first column is the date/time and the 2nd column is the data I'm interested in. I need to sort and smooth the data but I need to maintain that original index.

So if the first column starts in "E7" and ends at the bottom of "E8", would the code read like this?

bLevels = Range("E7", Range("E8").End(xlDown)).Value

Thanks!!


Matt

 

[SkipVought]

Assuming columns E & F

    Dim bLevels, i, j
    bLevels = Range(Range("E7:F7"), Range("E7:F7").End(xlDown)).Value
    For i = LBound(bLevels, 1) To UBound(bLevels, 1)
        For j = LBound(bLevels, 2) To UBound(bLevels, 2)
            Debug.Print bLevels(i, j)
        Next
    Next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MattGreer]

Thanks skip, always appreciated!

Thanks!!


Matt