Fill array till first empty cell.

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

Please Login or Register  to view this content.

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

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.

OK...change is good.

Please Login or Register  to view this content.

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

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.

Ack...

Losing the iteration has made my next step very hard.

By filling the entire array in one step:

Please Login or Register  to view this content.

I have lost the ability to look in Column B and use an if statement.

Something like;

IF Cell (i,2) = On
Then myarray_On = Cells(i, 3).Value
IF Cell (i,2) = Off
Then myarray_Off = Cells(i, 3).Value

That's not really code, I know, just short hand for a hunt and pecker.
Maybe I need to count the values in Column C and assign something like:
i = i +1 till i=count

Ack...

I am trying to fill an array.

DonkeyOte filled the array, which was great...but it voided my idea for the next step:

Example:

Col A Col B
Row 1 on bob
Row 2 off cat
Row 3 on dog
Row 4 off egg
Row 5 on fig
Row 6 off get
Row 7 on hit

(Please excuse the formatting of the table here shg...no amount of spacing would make it better)

I would like to fill the dynamic array iteratively from Col B. Something close to:

Please Login or Register  to view this content.

Where i starts at cell B1 and continues till the first blank cell in Col B.

I would like to fill it one cell at a time (iteratively) because I would also like to put the values from Col B into 2 other arrays: myArray_On & myArray_Off.
Obviously myArray_On would contain bob, dog, fig, and hit. While myArray_Off would contain cat, egg, and get. That is: fill from Col B based on the
corresponding value from Col A

My thought on how to accomplish this would be something akin to:

IF Cells(i,2).Value = on
Then myarray_On = Cells(i,3).Value

Which I can't figure out how to do using the code provided by DonkeyOte.

Please?

OK...I did it.

And it was simple.

And thank you DonkeyOte for the help. But using your Range method made it too difficult to use the array for later step. WAY to difficult..if possible.

Allow to post my solution for future generations.

Please Login or Register  to view this content.

And whalla!!!

I am still working on the dividing up array_names into 2 new arrays based on whether Col A contains the word on or off It's not working yet...but I really like the Select Case option supplied by DonkeyOte.

I'll post it here if I get it working...I'll also check back to see if someone else does the same (wink wink...nudge nudge)