Napaka "Subscript out of range"

Pomoč pri izdelavi makrov
Odgovori
PaPaDiZ
Prispevkov: 26
Pridružen: Pe Maj 11, 2007 7:01 am

Napaka "Subscript out of range"

Odgovor Napisal/-a PaPaDiZ »

Pozdravljeni

Rabil bi pomoč oz. ne morem ugotoviti zakaj spodnja koda v Excel-u 2010 deluje normalno, v Excelu 2007 pa javi napako: Run-time error '9': "Subscript out of range" v vrstici Set WB = Workbooks("Preveri Podatke_v1").

Hvala

Koda: Izberi vse

Sub PreveriPodatke()
        Dim b, c As Long
        Dim x, x1, x2 As Integer
        Dim TimeStart As Single, TimeEnd As Single
        Dim rng As Excel.Range
        Dim EndRow, LastRow As Long
        Dim WB, DZ As Workbook
        Dim WS, DS As Worksheet
        
        Set WB = Workbooks("Preveri Podatke_v1")
        Set WS = WB.Worksheets("Sheet2")
        
        Set DZ = Workbooks("Book1")
        Set DS = DZ.Worksheets("Sheet1")
        
        
        EndRow = WS.Range("A65536").End(xlUp).Row
        LastRow = DS.Range("B65536").End(xlUp).Row
       
        TimeStart = Timer
       
        b = WS.Range("A1").CurrentRegion.Resize(, 3).Value
        c = DS.Range("A1").CurrentRegion.Resize(, 4).Value

For i = 2 To LastRow
    x = 1
    
    Do While c(i, 2) <> b(x, 1) Or x > EndRow
        x = x + 1
        If x > EndRow Then
            x = EndRow
            Exit Do
        End If
        x1 = x
        
        Do While c(i, 2) = b(x1, 1) And c(i, 3) <> b(x1, 2) Or x1 > EndRow
            x1 = x1 + 1
            If x1 > EndRow Then Exit Do
            x2 = x1
            
            Do While c(i, 2) = b(x2, 1) And c(i, 3) = b(x2, 2) And c(i, 4) <> b(x2, 3)
                x2 = x2 + 1
            Loop
            
            If x1 < x2 And c(i, 3) <> b(x2, 2) And c(i, 4) <> b(x2, 3) Then
                DS.Cells(i, 4).Interior.ColorIndex = 40
                GoTo ByPass
            End If
            If x1 <= x2 And c(i, 3) = b(x2, 2) And c(i, 4) = b(x2, 3) Then
                GoTo ByPass
            End If
        Loop
        
        If x < x1 And c(i, 3) <> b(x1 - 1, 2) Then
            DS.Cells(i, 3).Interior.ColorIndex = 20
            DS.Cells(i, 4).Interior.ColorIndex = 40
            GoTo ByPass
        End If
        If x < x1 And c(i, 3) = b(x1 - 1, 2) Then
            GoTo ByPass
        End If
    Loop

ByPass:
    If x >= EndRow And c(i, 1) <> b(x, 1) Then
        DS.Cells(i, 2).Interior.ColorIndex = 19
        DS.Cells(i, 3).Interior.ColorIndex = 20
        DS.Cells(i, 4).Interior.ColorIndex = 40
    End If
Next i
        
        TimeEnd = Timer
        timetester = TimeEnd - TimeStart
        MsgBox timetester
End Sub
admin
Site Admin
Prispevkov: 3687
Pridružen: Sr Jul 20, 2005 10:06 pm

Re: Napaka "Subscript out of range"

Odgovor Napisal/-a admin »

Napaka "Subscript out of range" pomeni, da elementa ni v seznamu. Če vam VBA omenjeno napako javi na vrstici 'WB = Workbooks("Preveri Podatke_v1")' pomeni, da trenutno nimate odprtega dokumenta "Preveri Podatke_v1.
lp,
Matjaž Prtenjak
Administrator
Odgovori