1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Runtime error 9, Subscript out of Range

Discussion in 'Business Applications' started by iboumiza, Nov 17, 2011.

Thread Status:
Not open for further replies.
  1. iboumiza

    iboumiza Thread Starter

    Joined:
    Nov 16, 2011
    Messages:
    2
    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
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Is "RULES" a separate function that you have not included in your code?

    q = Split(RULES(i, 6), ";")


    Rollin
     
  3. iboumiza

    iboumiza Thread Starter

    Joined:
    Nov 16, 2011
    Messages:
    2
    fixed !

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

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1027217

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice