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
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