How do i solve this error 9 problem in vba

Question

When i run my vba script it says "subscript out of range"

Sub søgProjektNr2()

    Debug.Print "--------------------------------"
    Debug.Print "              test"
    Debug.Print "--------------------------------"

    Dim Nr As Integer
    Dim Lib(2 To 7, 1 To 10) As Variant
    Dim bil(1 To 10, 1 To 4) As Integer
    Dim LastRow As Long
    Dim typeDrop As Integer
    Dim c As Integer
    Dim p As Integer
    Dim i As Integer
    
    For i = 2 To 6
        If Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Row > LastRow Then
            LastRow = Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Row
        End If
    Next i
    
    For i = 2 To LastRow
        Worksheets(2).Rows(i).Value = ""
    Next i
    
    Debug.Print LastRow
    
        '1 = bemærkninger
        '2 = dokumenttitel
        '3 = sider
        '4 = rev
        '5 = rev dato
        '6 = kat.
        
        Lib(4, 1) = 8
        Lib(5, 1) = 8
        Lib(6, 1) = 6
        Lib(7, 1) = 7
        
        Lib(4, 2) = 2
        Lib(6, 2) = 2
        
        Lib(4, 3) = 4
        Lib(5, 3) = 4
        Lib(6, 3) = 3
        Lib(7, 3) = 4
        
        Lib(4, 4) = 6
        Lib(5, 4) = 6
        Lib(6, 4) = 4
        Lib(7, 4) = 5
        
        Lib(4, 5) = 7
        Lib(5, 5) = 7
        Lib(6, 5) = 5
        Lib(7, 5) = 6
        
        Lib(4, 6) = 9
        Lib(5, 6) = 9
        Lib(6, 6) = 7
        Lib(7, 6) = 8
        
        bil(1, 1) = 4
        bil(1, 2) = 5
        bil(1, 3) = 6
        bil(1, 4) = 7
        
        
        Nr = cbprojektnr.Value
        
        c = 1
        
        Debug.Print UBound(bil, 2)
        
        
    'For p = LBound(bil, 1) To UBound(bil, 1)
    For p = 4 To 7
        
        Debug.Print "H" & bil(1, p) & " " & Nr
        
            For i = 2 To LastRow
                
                If Worksheets(p).Cells(i, 1).Value = Nr Then
        
                    Debug.Print bil(1, p) & "T" & i
        
                    c = c + 1
                    Worksheets(2).Cells(c, 1).Value = Nr
                    Worksheets(2).Cells(c, 6).Value = Worksheets(p).Cells(i, Lib(p, 1)).Value
                    Worksheets(2).Cells(c, 2).Value = Worksheets(p).Cells(i, Lib(p, 2)).Value
                    Worksheets(2).Cells(c, 3).Value = Worksheets(p).Cells(i, Lib(p, 3)).Value
                    Worksheets(2).Cells(c, 4).Value = Worksheets(p).Cells(i, Lib(p, 4)).Value
                    Worksheets(2).Cells(c, 5).Value = Worksheets(p).Cells(i, Lib(p, 5)).Value
                    Worksheets(2).Cells(c, 7).Value = Worksheets(p).Cells(i, Lib(p, 6)).Value
                End If
        
            Next i
    
    Next p
    
        Debug.Print ""
        Debug.Print ""
        Debug.Print ""
    
End Sub

ive tried running the code where i expected it would take cell value from different clolumns what i mean is that the same type of columns is differently numbered and ive created the lib array as a sort of libary for finding their number

what happen is that it says subscript out of range and i couldnt locate the problem with breakpoints i also saw with debug that it has looped through atleast one time through but with a different issue its very confusing for me please help

Can you try changing `Lib` to a different naming? As you can see in your post, it's colored blue already which is a red flag to me indicating you're using a name that's used by the VBE for something else. You're also filling `bil` from 1,1 to 1,4 but then printing it 1,4 to 1,7? Let us know if that didn't fix it. P.S.: try not to hardcode so much, you'll have a lot more to adjust if something changes and it'll break easier. — Notus_Panda, Apr 24 '23 at 08:21

score 0 · Answer 1 · answered Apr 24 '23 at 09:29

When VBA detect a run-time error it breaks on the line. When you get the message of the error, click Debug. The line which caused the error will be colored (default yellow). Here you can check in the Immediate Window all of your variables values, and do commands also. This message generally means that one of the arguments is beyond the limits. e.g. The arrays item value is greater than the array's size. In the Watch Window can check continuously the selected variables values or expressions result as you like. With Step execution can check at every line the result of anything.

How do i solve this error 9 problem in vba

1 Answers1