Excel cell values into an array

[thebloke]

Guys

I have a spreadsheet with a column of data (dates, to be exact) that could potentially change. I have some code which does what I want to do very well except the current values in the column are hard-coded into an array, thus:

myArray = Array("01/10/2003", "01/11/2003", "01/12/2003", "01/01/2004", "01/02/2004", "01/03/2004", "01/04/2004", "01/05/2004", "01/06/2004", "01/07/2004", "01/08/2004", "01/09/2004", "01/10/2004", "01/11/2004", "01/12/2004", "01/01/2005")

As these dates may change or indeed be added to, I want to be able to dynamically build the array. I've tried the following, to no avail:

Do Until x = intRows
    
    ActiveSheet.Cells(x, 7).Activate
    
    
        If strarray = "" Then
        
            strarray = ActiveSheet.Cells(x, 7) & Chr(34)
    
        Else
            strarray = strarray & "," & Chr(34) & ActiveSheet.Cells(x, 7) & Chr(34)
                    
        End If
        
        x = x + 1
    Loop
 '   MsgBox strarray
 
    intlength = Len(strarray) - 1
    strarray = Left(strarray, intlength)
    myArray = Array(strarray)

The problem seems to be that although strarray builds up and contains all the correct data, myArray is never populated. I'm pretty sure I'm doing something stupid but I'm just not sure what.

Cheers

[si_the_geek]

you can't just say "make an array out of a string" and expect it to convert it how you want it (it will just make an array with one element).

Fortunately there is a nice function called Split, which does the conversion for you. This is what you need in your current code:

myArray = Split(strarray, chr(34))


There are also two alternatives - the first is to put individual items into the array (rather than into the string and then converting to an array). Lke this:

Dim myArray
    Redim myArray (intRows)
 
    Do Until x = intRows
      myArray(x) = ActiveSheet.Cells(x, 7)
      x = x + 1
    Loop

Also, you can get the full array in one go, without having to loop.

myArray = ActiveSheet.Range("G1:G" & intRows).FormulaArray

I'm not sure .FormulaArray is right, it might need to be .Value , or .Text , or be left out completely.

[thebloke]

Cheers Si, I'll give it a go.

Tar lar.