0

i need to copy data from an Excel range into an array. I am using the following code, but it is showing the error "expected array."

  Dim filename As String
  Dim arraysize As Integer

  arraysize = 50
  I = 1
  Do Until ThisWorkbook.Sheets("sheet1").Cells(I, 1).Value = ""   
     filename(arraysize) = ThisWorkbook.Sheets("sheet1").Cells(I, 1).Value    
     I = I + 1
  Loop
Community
  • 1
  • 1
user2070732
  • 1

2 Answers2

1

try this

Sub Demo()
    Dim filename As Variant
    Dim arraysize As Long
    Dim rng As Range
    Dim i As Long

    i = 1
    With ThisWorkbook.Sheets("sheet1")
        Set rng = .Range(.Cells(i, 1), .Cells(i, 1).End(xlDown))
    End With

    ' load as two dimensional array
    filename = rng.Value

    ' transform into 1 dimensional array
    filename = Application.Transpose(filename)

    arraysize = UBound(filename)
End Sub
chris neilsen
  • 52,446
  • 10
  • 84
  • 123
  • 1
    + 1 Nicely Done :) I guess the use of `i` is not required and can be plugged in the code it self :) I am also kind of skeptical on the use of `xlDown` to find the lastrow. My fav method is http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba – Siddharth Rout Feb 14 '13 at 06:38
  • Thank Siddharth. I left `i` in on the assumption this is a code fragment of a larger app, where `i` has some significance. I used `xlDown` to replicate the OP code behaviour - as you know, this acts from `i` down to the first blank row, which many or may not be the last used row (although I admit I haven't covered the cases where cell at `i` is blank, or there is only one non-blank cell starting at `i`) – chris neilsen Feb 14 '13 at 07:02
-1

You haven't declared the array. I can't think of the code off the top of my head but this should point you in the right direction.

Dim Filename(50)

Do
    Filename(I)= excel.row
Loop
danabnormal
  • 462
  • 2
  • 8