PDA

View Full Version : Getting "Subscript out of range" error



iuliamihaela
09-16-2013, 07:13 AM
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 SubAny help will be appreciated. Thank you very much,
Iulia

Jan Karel Pieterse
09-16-2013, 07:47 AM
Hi Iulia,

On which line exactly?

iuliamihaela
09-16-2013, 08:18 AM
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
09-16-2013, 08:33 AM
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
09-16-2013, 06:58 PM
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
09-17-2013, 08:19 AM
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/e2290cd40aff86be093e3d7c3737180520130917143302/8ebd9a6da740626c15af664ff0fb0b9920130917143302/0835d8

Thank you very much!
Iulia

SamT
09-17-2013, 12:56 PM
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
09-17-2013, 02:05 PM
I think this is not a valid codeline:

If arr1(4) arr2(4) Then

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

SamT
09-17-2013, 03:22 PM
:razz:

Kenneth Hobs
09-17-2013, 08:57 PM
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 (http://en.wikipedia.org/wiki/Metre_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
09-17-2013, 10:45 PM
define your public variables with range

snb
09-18-2013, 02:23 AM
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