Getting "Subscript out of range" error

[iuliamihaela]

I am trying to run the code below and I am getting the "Subscript out of range" error. Can you please help me understand what is wrong?

Sub check1_standard(s, f As Variant)
Dim p_r(0 To 100) As Integer
Dim l_r(0 To 100) As Integer
Dim e_r(0 To 100) As Integer
A = False
l = True
e = True
ms = 0
m = True
p = True
p2 = True
ds = 0
p_r(0) = 0
l_r(0) = 0
e_r(0) = 0
n_a = 0
m_r = 0
For x = s To f
    If Cells(x, 5).Value = "A" Then
        n_a = n_a + 1
        A = True
        'If Cells(x, 10).Value = "" Or Cells(x, 12).Value = "" Then Exit Sub
        arr1 = Split(Cells(x, 10).Value, ",")
        arr2 = Split(Cells(x, 12).Value, ",")
                If arr1(4)  arr2(4) Then
            l = False
            l_r(0) = l_r(0) + 1
            l_r(l_r(0)) = x
        End If
                If Left(arr2(3), 3) = "ESD" Then
            e = False
            e_r(0) = e_r(0) + 1
            e_r(e_r(0)) = x
        End If
                If Cells(x, 4).Value = "CR" Then
            ms = ms + 1
            If arr2(3)  "UAOO" Then m = False
        End If
        If Cells(x, 4).Value = "ED" Then
            ms = ms + 15
            If arr2(3)  "AOO" Then m = False
        End If
        If Cells(x, 4).Value = "GV" Then
            ms = ms + 100
            If arr2(3)  "UAOO" Then m = False
        End If
        If Cells(x, 4).Value  "" Then
            If (arr1(2) = "WIN" And arr2(2) = "MAC") Or (arr1(2) = "MAC" And arr2(2) = "WIN") Then
                p = False
                p_r(0) = p_r(0) + 1
                p_r(p_r(0)) = x
            End If
            Else
            If arr2(2) = "WIN" Then ds = ds + 1
            If arr2(2) = "MLP" Then ds = ds + 1
            If arr2(2) = "MAC" Then ds = ds + 10
            m_r = x
        End If
    End If
Next x
If ms < 116 Then m = False
If ds  11 Then p2 = False
If e = False Or n_a > 5 Or p = False Or p2 = False Or m = False Or A = False Or l = False Then Range("A" & s & ":M" & f).Interior.Color = RGB(255, 255, 0)
If A = False Then
    Cells(s, 3).Value = "No Active SKU"
Else
    If p = False Then
        For i = 1 To p_r(0)
            Cells(p_r(i), 3).Value = "Check Platform"
        Next
    End If
    If e = False Then
        For i = 1 To e_r(0)
            Cells(e_r(i), 3).Value = "ESD Fulfillment"
        Next
    End If
        If m = False Then Cells(s + 2, 3).Value = "Check Market"
    If l = False Then
    For i = 1 To l_r(0)
            Cells(l_r(i), 3).Value = "Check Language"
        Next
    End If
    If n_a > 5 Then
        Cells(s + 4, 3).Value = "Too Many Active SKUs"
        Else
        If m_r = 0 Then
            If p2 = False Then Cells(s + 1, 3).Value = "Check Media"
            Else
            If p2 = False Then Cells(m_r, 3).Value = "Check Media"
        End If
    End If
End If
Range("A" & s & ":M" & f).BorderAround (xlContinuous)
End Sub

Any help will be appreciated. Thank you very much,
Iulia

[Jan Karel Pieterse]

Hi Iulia,

On which line exactly?

[iuliamihaela]

Hi, This is the issue - I don't know which line it is because none turns yellow and there is no debug option when the error shows up. Do you know a way to find out where does it occur? Thanks! Iulia

[Paul_Hossler]

1. Assuming that you pasted in correctly, then there's a LOT of syntax errors, like :

  If arr2(3)  "UAOO" Then m = False

2. I'd suggest that more meaningful variable names would help also. These are not very meaningful to anyone other than the original programmer

    Dim p_r(0 To 100) As Integer
    Dim l_r(0 To 100) As Integer
    Dim e_r(0 To 100) As Integer
    A = False
    l = True
    e = True
    ms = 0
    m = True
    p = True
    p2 = True
    ds = 0

3. I like to use this at the top of each module to require specific Dim-ing of all variables

Option Explicit

Dim A as Boolean, l as Boolean, e as Boolean, etc.
Dim ds as Long

4. It may or may not matter, but each variable in a Dim line might need the ' As ' part, otherwise it's assumed to be a Variant. Same for Sub parameters

Option Explicit

Sub check1_standard(s As Variant, f As Variant)

Dim A, l, e as Boolean    ' A and l are Variant, e is Boolean

Dim A as Boolean, l as Boolean, e as Boolean    ' A and l and e are Boolean

5. If you're still not getting a Debug option, maybe you should post a small version of your workbook with the macro

Paul

[SamT]

For x = s To f 
        If Cells(x, 5).

Both s and f are = 0, the initial value of all number variables.

As has been mentioned, Use "Option Explicit" at the top of all code pages. And don't be lazy, use meaningful names for all variables. Otherwise, it will bite you later.

[iuliamihaela]

Hi,
First of all, thanks for your messages. I have tried what you have suggested, but I am still getting errors:
When using Option Explicit, I am receiving an error "Variable not defined" - for Subs.
If I don't turn on Option Explicit, I am getting the same error - "Subscript out of range".
I am not able to attach files to forum entries (or I don't know how...the Manage attachments button does not work), therefore I have uploaded a copy of the file and the test file I am using here - https://www.wetransfer.com/downloads...7143302/0835d8

Thank you very much!
Iulia

[SamT]

For x = s To f 
    If Cells(x, 5)

Both s and f are undefined, that means that you did not Explicitly Declare them. To Declare a variable means to "Dim" them. When you have Option Explicit, it means that if you use a variable without Explicitly Declaring it, VBA will raise an Error and highlight the first undeclared variable the first time the code is Compiled. You can manually Compile your code with the Debug menu button/command

When a Variable is declared, either Explicitly or Implicitly, its initial value is set to the appropriate zero or Null value for its Type. For Strings, the initial value is an empty string (""); for numbers, it is zero (0).

In the above code segment, all three variables, x, s, and f are both undeclared and = 0, giving both Undeclared Variable and Subscript Out Of Range Errors.

I can't offer any more help than the above because none of your variable names have any meaning and that makes your code meaningless too.

[snb]

I think this is not a valid codeline:

If arr1(4) arr2(4) Then

I also second the person who introduced E=mc² and didn't use 'meaningful' variable names.

[SamT]

[Kenneth Hobs]

Try to learn how to use the forum's software for attachments. Most forums that allow attachments are of this type. http://www.youtube.com/watch?v=-cPGH6VU9NE

It looks like you have placed all code in ThisWorkbook object. Use a Module. After Compiling code, use Debug to step (F8) through your code line by line to see where the error occurs.

After pasting your code, as snb noted, the first syntax error is at:

            If arr1(4)  arr2(4) Then

Add the Compile button to the VBE toolbar and compile code before running it.

Use Option Explicit as first line of code in objects like Modules. You had it in the middle of code.

Using all Variant variable types is not efficient. Try to avoid Variants when possible.

Variable naming convention should be used generally. It makes sense to make your variable names mean something. e.g. tfDelete=a boolean value to Delete, True or False. I guess you could use c, but someone else made that mean the speed of light in vacuum 299,792,458 metres per second. Of course you can use c to mean whatever you like. Just don't expect anyone else to know what you mean unless you comment it in your code. Of course that is a good idea anyway.

When debugging a Sub like that, make a short Sub and pass variable values to the other Sub and Step through that code. You need to fix the obvious syntax errors first.

You have alot of IF/Else/Then structures. Try to use Select Case or arrays to make code not only easier to read but faster and easier to maintain.

[abraxus]

define your public variables with range

[snb]

you'd better start cleaning the macros from the start:

Sub browse_sent()
    Sheet2.Range("C5").Value = Application.GetOpenFilename
End Sub

Public Sub start_standard()
    open_file
    Sheet2.Range("P1").Value = "standard"
    check_initial "standard", False
End Sub

Sub open_file()
  With Workbooks.Open(Sheet2.Range("C5").Value)
    With .Sheets(1).UsedRange
       ThisWorkbook.Sheets("report").Cells(1).Resize(.Rows.Count, .Columns.Count) = .Value
    End With
    .Close False
   End With
End Sub

Sub check_initial(what As String, change As Boolean, Optional s1 As Variant, Optional f1 As Variant)
   If change Then
     If what = "standard" Then check1_standard s1, f1
   Else
    UsedRange.Borders = xlNone
    With UsedRange.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    If Range("C1").Value <> "Reason" Then
        Columns(3).Insert
    Else
        Columns(3).ClearContents
    End If
    Range("C1") = "Reason"
    
    Sheet1.UsedRange.Sort Cells(1, 1), , Cells(1, 2), , , Cells(1, 5), , xlYes
    
    If what = "standard" Then
        sn = Columns(1).SpecialCells(1)
        For j = 2 To UBound(sn)
          If sn(j) <> sn(j - 1) Then check1_standard
        Next
    End If
  End If
End Sub