Runtime error with "do...until"loop

[shabbyporpoise]

Hello Forum I'm new to excel vba, I have been using this forum quite a bit to search for similar problems. I have found the forum very helpful. With my work we use excel quite a bit, and my employer wants me to learn vba to streamline the worksheets we use. My program has nothing to do with work, it is a personal project however everything would apply to work as well. back story done. I am encountering a Runtime error 1004 "Application-defined or object-defined error I have a list of 500 movies in column A, I am trying to have a loop that goes through the entire list, stops when there are no more items and put all of these items in an array. Thanks for your help forum ---------------------- Option Explicit Sub movieList() Dim num As Integer, names As String, i As Integer Dim rowCount As Variant, myMovies() As Variant i = 0 Do Until IsEmpty(Cells(i, 1)) rowCount = Array("a1" & i) myMovies(i) = Cells(i, 2).Value i = i + 1 MsgBox myMovies(i) Loop End sub -----------------

 

[shabbyporpoise]

I am trying to figure out how to input my code in the nice windows that everyone else is. I am very new to this website. and to forums in general. Please be patient please tell me how to make my posts look nice Thanks

 

[VoG]

Use code tags:

[code]
paste your code here
[/code]

 

[shabbyporpoise]

awesome thanks VoG!!

 

[shabbyporpoise]

Hello Forum I'm new to excel vba, I have been using this forum quite a bit to search for similar problems. I have found the forum very helpful. With my work we use excel quite a bit, and my employer wants me to learn vba to streamline the worksheets we use. My program has nothing to do with work, it is a personal project however everything would apply to work as well. back story done. I am encountering a Runtime error 1004 "Application-defined or object-defined error" I have a list of 500 movies in column A, I am trying to have a loop that goes through the entire list, stops when there are no more items and put all of these items in an array. Thanks for your help forum ----------------------

 Option Explicit  
Sub movieList()     
Dim num As Integer, names As String, i As Integer    
 Dim rowCount As Variant, myMovies() As Variant         
 i = 0     
Do Until IsEmpty(Cells(i, 1))        
 rowCount = Array("a1" & i)         
myMovies(i) = Cells(i, 2).Value        
 i = i + 1         
MsgBox myMovies(i)    
 Loop

-----------------

 

[shabbyporpoise]

ok I know it looks pitiful.... are there line breaks that I'm not aware of? Thanks for being patient with a newb.

 

[VoG]

Maybe

Sub movieList()
Dim num As Integer, names As String, i As Integer
Dim rowCount As Variant, myMovies()
i = 0
Do Until IsEmpty(Cells(i, 1))
    rowCount = Range("A" & i)
    ReDim Preserve myMovies(1 To i)
    myMovies(i) = Cells(i, 2).Value
    i = i + 1
    MsgBox myMovies(i)
Loop

 

[shabbyporpoise]

VoG- the changes you suggested worked great! this was a huge help. A quick question I have about your code(if your too busy don't worry about it) how come you did not put "as variant" after myMovies()?

thanks again VoG

 

[VoG]

It will work with Variant but you need to initialize i = 1 and move the line where i is incremented

Sub movieList()
Dim num As Integer, names As String, i As Integer
Dim rowCount As Variant, myMovies() As Variant
i = 1
Do Until IsEmpty(Cells(i, 1))
    rowCount = Range("A" & i)
    ReDim Preserve myMovies(1 To i)
    myMovies(i) = Cells(i, 2).Value
    MsgBox myMovies(i)
    i = i + 1
Loop
End Sub