Fill array till first empty cell.

**UncleDucc** · 10-02-2009, 03:11 PM

I thought that this would be so easy. Here's what I have:

Sub make_array3()
Dim myarray() As Variant
     Do
        myarray(i) = Sheets("Sheet1").Cells(i, 3).Value
        i = i + 1
    Loop Until IsEmpty(i)
        
Sheets("Sheet1").Range("D2:D10").Value = myarray

End Sub

The excel cells with values start in C1 on the Sheet1 tab.
I get a run time error 1004 in the line: myarray(i) = Sheets("Sheet1").Cells(i, 3).Value

I am only recopying the data to Column D to see if it is working.
Please tell me it is something simple.

ducc

**shg** · 10-02-2009, 03:47 PM

In the initial iteration, i is zero, and there is no row zero.

If you fixed that, the IsEmpty(i) would never be true.

What (exactly) do you want the code to do?

**UncleDucc** · 10-02-2009, 04:13 PM

I have text in Col C of sheet Sheet1.

I would like the values in Col C to be entered into the array 'myarray'.

I would like to stop this process when the first empty cell in Col C is reached.

**UncleDucc** · 10-02-2009, 04:33 PM

OK...change is good.

Sub make_array3()


Dim myarray() As Variant


    Do
        i = 1
        myarray(i) = Sheets("Sheet1").Cells(i, 3).Value
        i = i + 1
    Loop Until IsEmpty(Cells(i, 3))
        

Sheets("Sheet1").Range("D2:D10").Value = myarray


End Sub

But now I get a Script out of range error in the line:
myarray(i) = Sheets("Sheet1").Cells(i, 3).Value

**DonkeyOte** · 10-02-2009, 05:17 PM

If you have no values below the first blank another alternative avoiding iteration might be:

Public Sub Example()
Dim myArray As Variant
With Sheets("Sheet1")
    myArray = .Range(.Cells(1, "C"), .Cells(.Rows.Count, "C").End(xlUp))
    .Cells(2, "D").Resize(UBound(myArray, 1)).Value = Application.Index(myArray, 0, 1)
End With
End Sub

**UncleDucc** · 10-02-2009, 05:44 PM

Without a doubt it works.
(How does the topic get a [solved]...is that my job?)

I don't understand s few of the pieces. And I would still love to know where
I went wrong in the code I was building. But I can move on with what
you provided me.

Thank you DonkeyOte. May all your Wind Mills be Dragons.