Run-time error 1004, occuring randomly

[reinart]

Hello,

I have some problem with my VBA code. For some reason the code works great at one moment but then gives me run-time error 1004 frome time- to time.

Debugging stops at:

            andmed(m, n) = Worksheets("Sheet1").Cells(jada(m), n)

The code itself looks like this:

Sub start()
Dim algus As Integer
Dim rida(1 To 5) As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim m As Integer
Dim n As Integer
Dim o As Integer
Dim p As Integer
Dim q As Integer
Dim temp1 As Variant
Dim andmed(1 To 5, 1 To 4) As String
Dim jada() As Integer
Dim kystyyp As Integer
Dim kysjada(1 To 5) As Integer
Dim asukoht As String

Dim oiged As Integer
Dim valed As Integer

asukoht = Application.ActiveWorkbook.Path

algus = 1
ridu = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

ReDim jada(ridu)
    For i = 1 To ridu
        jada(i) = i
    Next i

    For o = 1 To 5
        kysjada(o) = o
    Next o
    

UserForm1.Show
kysarv = UserForm1.TextBox1.Text

    For l = 1 To kysarv
    
    UserForm2.valik1.Value = False
    UserForm2.valik2.Value = False
    UserForm2.valik3.Value = False
    UserForm2.valik4.Value = False
    UserForm2.valik5.Value = False

    Randomize
        For j = LBound(jada) To UBound(jada)
            k = CLng(((UBound(jada) - j) * Rnd) + j)
            If j <> k Then
                temp1 = jada(j)
                jada(j) = jada(k)
                jada(k) = temp1
            End If
        Next j
        
        For p = LBound(kysjada) To UBound(kysjada)
            q = CLng(((UBound(kysjada) - p) * Rnd) + p)
            If p <> q Then
                temp2 = kysjada(p)
                kysjada(p) = kysjada(q)
                kysjada(q) = temp2
            End If
        Next p
             
        For m = 1 To 5
            For n = 1 To 4
                                                
            andmed(m, n) = Worksheets("Sheet1").Cells(jada(m), n)
            
            MsgBox andmed(m, n)
            
            Next n
        Next m
        
        kystyyp = (4 - 1 + 1) * Rnd + 1
        
            If kystyyp = 1 Then
                UserForm2.kysimus.Caption = "Mis on korp! " & andmed(1, 1) & " värvid?"
                UserForm2.Image1.Picture = LoadPicture(asukoht & "\hall.jpg")
                    
                    UserForm2.valik1.Caption = andmed(kysjada(1), 2)
                    UserForm2.Image2.Picture = LoadPicture(asukoht & "\" & andmed(kysjada(1), 2) & ".jpg")
                    UserForm2.valik2.Caption = andmed(kysjada(2), 2)
                    UserForm2.Image3.Picture = LoadPicture(asukoht & "\" & andmed(kysjada(2), 2) & ".jpg")
                    UserForm2.valik3.Caption = andmed(kysjada(3), 2)
                    UserForm2.Image4.Picture = LoadPicture(asukoht & "\" & andmed(kysjada(3), 2) & ".jpg")
                    UserForm2.valik4.Caption = andmed(kysjada(4), 2)
                    UserForm2.Image5.Picture = LoadPicture(asukoht & "\" & andmed(kysjada(4), 2) & ".jpg")
                    UserForm2.valik5.Caption = andmed(kysjada(5), 2)
                    UserForm2.Image6.Picture = LoadPicture(asukoht & "\" & andmed(kysjada(5), 2) & ".jpg")
                    
                UserForm2.Show
                
                If andmed(1, 2) = vastus Then
                    MsgBox "Õige vastus!"
                Else
                    MsgBox "Vale vastus!"
                End If
                                    
            Else
                If kystyyp = 2 Then
                    UserForm2.kysimus.Caption = "Mis korp! värvid on " & andmed(1, 2) & "?"
                    UserForm2.Image1.Picture = LoadPicture(asukoht & "\" & andmed(1, 2) & ".jpg")
                    
                        UserForm2.valik1.Caption = andmed(kysjada(1), 1)
                        UserForm2.Image2.Picture = LoadPicture(asukoht & "\hall.jpg")
                        UserForm2.valik2.Caption = andmed(kysjada(2), 1)
                        UserForm2.Image3.Picture = LoadPicture(asukoht & "\hall.jpg")
                        UserForm2.valik3.Caption = andmed(kysjada(3), 1)
                        UserForm2.Image4.Picture = LoadPicture(asukoht & "\hall.jpg")
                        UserForm2.valik4.Caption = andmed(kysjada(4), 1)
                        UserForm2.Image5.Picture = LoadPicture(asukoht & "\hall.jpg")
                        UserForm2.valik5.Caption = andmed(kysjada(5), 1)
                        UserForm2.Image6.Picture = LoadPicture(asukoht & "\hall.jpg")
                    
                    UserForm2.Show
                    
                    If andmed(1, 1) = vastus Then
                        MsgBox "Õige vastus!"
                    Else
                        MsgBox "Vale vastus!"
                    End If
                 
                Else
                    If kystyyp = 3 Then
                        UserForm2.kysimus.Caption = "Millal asutati on korp! " & andmed(1, 1) & "?"
                        UserForm2.Image1.Picture = LoadPicture(asukoht & "\" & andmed(1, 2) & ".jpg")
                        
                            UserForm2.valik1.Caption = andmed(kysjada(1), 3)
                            UserForm2.Image2.Picture = LoadPicture(asukoht & "\hall.jpg")
                            UserForm2.valik2.Caption = andmed(kysjada(2), 3)
                            UserForm2.Image3.Picture = LoadPicture(asukoht & "\hall.jpg")
                            UserForm2.valik3.Caption = andmed(kysjada(3), 3)
                            UserForm2.Image4.Picture = LoadPicture(asukoht & "\hall.jpg")
                            UserForm2.valik4.Caption = andmed(kysjada(4), 3)
                            UserForm2.Image5.Picture = LoadPicture(asukoht & "\hall.jpg")
                            UserForm2.valik5.Caption = andmed(kysjada(5), 3)
                            UserForm2.Image6.Picture = LoadPicture(asukoht & "\hall.jpg")
                        
                        UserForm2.Show
                                        
                        If andmed(1, 3) = vastus Then
                            MsgBox "Õige vastus!"
                        Else
                            MsgBox "Vale vastus!"
                        End If
                 
                    Else
                        UserForm2.kysimus.Caption = "Kus asutati on korp! " & andmed(1, 1) & "?"
                        UserForm2.Image1.Picture = LoadPicture(asukoht & "\" & andmed(1, 2) & ".jpg")
                        
                            UserForm2.valik1.Caption = andmed(kysjada(1), 4)
                            UserForm2.Image2.Picture = LoadPicture(asukoht & "\hall.jpg")
                            UserForm2.valik2.Caption = andmed(kysjada(2), 4)
                            UserForm2.Image3.Picture = LoadPicture(asukoht & "\hall.jpg")
                            UserForm2.valik3.Caption = andmed(kysjada(3), 4)
                            UserForm2.Image4.Picture = LoadPicture(asukoht & "\hall.jpg")
                            UserForm2.valik4.Caption = andmed(kysjada(4), 4)
                            UserForm2.Image5.Picture = LoadPicture(asukoht & "\hall.jpg")
                            UserForm2.valik5.Caption = andmed(kysjada(5), 4)
                            UserForm2.Image6.Picture = LoadPicture(asukoht & "\hall.jpg")
                        
                        UserForm2.Show
                                                                
                        If andmed(1, 4) = vastus Then
                            MsgBox "Õige vastus!"
                        Else
                            MsgBox "Vale vastus!"
                        End If
                        
                    End If
                
                End If
                
            End If
            

    Next l

End Sub

It seemingly occures randomly.

I upladed the whol workbook and related images here: http://reinart.planet.ee/akorgd.rar

Any suggestions?


Best wishes,
Reinart

 

[RickXL]

Hi.

It is failing when jada(m)=0.

lbound(jada) is zero. As you do not set that value in your For loop it remains zero so when you assign something to jada(0) it will be set to zero. As there is no row zero then you get the 1004 error.

Should jada start at 0 or 1?