Excel [VBA + Excel 2k3] Runtime error 9, Subscript out of Range

I

iboumiza

Joined
Nov 17, 2011
Messages
2
Reaction score
0
Hi,


I have a huge problem:
I have an Excel spreadsheet that links to Access database to pull data and use access table to render excel format with the data in it.
But, when I hit Add/Modify button I receive this Runtime error 9 and debug mode send me to this code:

Code: 
Private Sub ADDC_Click() 
    Call boot_rules(1) 
    testresult = testvalidsave 
     
    If testresult = "" Then 
        Dim q As Variant 
        Dim ff As String 
         
        q = Split(ctype.Text, " - ") 
         
        On Error Goto 0 
         
        Set db = OpenDatabase(tset(q(0)), False, False, "MS Access;pwd=password1") 
        If ADDC.Caption = "Modify" Then 
            Set rs = db.OpenRecordset("SELECT * FROM DB WHERE pk_id = " & sellinfo(0, 0)) 
        Else 
            Set rs = db.OpenRecordset("DB", dbOpenTable) 
        End If 
        Erase q 
         
        With rs 
             
            .AddNew 
            If ADDC.Caption = "Modify" Then 
                If Not .BOF Then .MoveFirst 
                .Edit 
            Else 
                .AddNew 
            End If 
            i = 1 
            While Not RULES(i, 1) = "" 
                If RULES(i, 1) = "order_date" Then 
                    q = Split(RULES(i, 15), ";") 
                    .fields("order_date") = Cells(Int(q(0)), Int(q(1))).Value 
                    .fields("YYM") = Year(Cells(Int(q(0)), Int(q(1))).Value) & "-" & Month(Cells(Int(q(0)), Int(q(1))).Value) 
                ElseIf RULES(i, 2) = "X" Then 
                    q = Split(RULES(i, 6), ";") 
                    If Not Cells(Int(q(0)), Int(q(1))).Value = "" Then 
                        .fields(RULES(i, 1)) = 1 
                    Else 
                        .fields(RULES(i, 1)) = 0 
                    End If 
                     
                Else 
                    q = Split(RULES(i, 15), ";") 
                    If Not RULES(i, 1) = "#" Then 
                        If Not RULES(i, 1) = "CC" Then 
                            If Not RULES(i, 1) = "city" Then 
                                If Not RULES(i, 1) = "MM" Then 
                                    If Not RULES(i, 1) = "EE" Then 
                                        .fields(RULES(i, 1)) = Cells(Int(q(0)), Int(q(1))).Value 
                                    End If 
                                End If 
                            End If 
                        End If 
                    End If 
                End If 
                i = i + 1 
            Wend 
             
             
             
            .fields("LOG") = .fields("LOG") & Now & " by ;" & GetUserName() & ";" 
             
             
            .fields("sell_type") = ctype.Text 
            .fields("city") = CC.Text 
            .fields("master_client") = MM.Text 
            .fields("client") = EE.Text 
            If cell.Visible Then 
                If cell Then 
                    .fields("GI4") = 1 
                Else 
                    .fields("GI4") = 0 
                End If 
            End If 
             
            If signed.Visible Then 
                If signed Then 
                    .fields("signed") = "signed" 
                Else 
                    .fields("signed") = "unsigned" 
                End If 
            End If 
            .Update ' stores the new record
        End With 
         
         
        rs.Close 
        Set rs = Nothing 
        db.Close 
        Set db = Nothing 
        ctype.Text = "Choose a sell Type" 
        Cells(1, 1).Select 
    Else 
        MsgBox (testresult) 
    End If 
     
End Sub

and it stops here:
Code: 
If Not Cells(Int(q(0)), Int(q(1))).Value = "" Then

How can I fix this, please?

Best Regards
iboumiza
 
fixed !

Code: 
if ubound(q)>=1 then
If Not Cells(Int(q(0)), Int(q(1))).Value = "" Then
........
end if
 
Back
Top