[RESOLVED] Runtime error 9, Subscript out of Range

[hackedman]

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:

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:

  If Not Cells(Int(q(0)), Int(q(1))).Value = "" Then

How can I fix this, please?

Best Regards

[opus]

The error occurs because your array q doesn't have as much elements as you are calling. Your asking to look for elements 0 and 1, you have to make sure they exist before asking for them.
for example:

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

[hackedman]

Thank you!!!!!!

problem finally solved