Subscript out of range error
 

I have been handed a VBA project which contains the following code (See
end). When I run the project I get the following error.

Encoutered a problem. Please ensure the column name has not been
amended. If the problem persists please report to administrator.


ErrorDes: Subscript out of range ErrorID:9 ErrorSource: VBAProject

*Can anybody please clarify why I am getting this error and how I
should resolve it. *


Dim sheeP As String

sheeP = ActiveSheet.Name

Dim dic1 As Object, x1, r1 As Range
Dim dic2 As Object, x2, r2 As Range
Dim p, q, s, fnlNumb As Variant
Set dic1 = CreateObject("Scripting.Dictionary")
Set dic2 = CreateObject("Scripting.Dictionary")

With Cells
Set c = .Find("Sell-to", LookIn:=xlValues, LookAt:=xlPart)
If Not c Is Nothing Then
firstAddress = c.AddressLocal
Do
add1 = c.AddressLocal

With Sheets(sheeP)
For Each r1 In .Range(add1)

p = Split(r1, " ")
q = Split(p(2), "(")
s = Split(q(1), ")")
fnlNumb = s(0)

If Not IsEmpty(r1) And Not dic1.exists(p(3) & " " & p(4) & " "
& fnlNumb) Then

dic1.Add p(3) & " " & p(4) & " " & fnlNumb, fnlNumb

End If
Next
End With

Set c = .FindNext(c)

Loop While Not c Is Nothing And c.AddressLocal < firstAddress

End If
End With


--
moglione1
------------------------------------------------------------------------
moglione1's Profile: http://www.excelforum.com/member.php...o&userid=26414
View this thread: http://www.excelforum.com/showthread...hreadid=400374

Usually means the object doesn't exist. Which line?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"moglione1" wrote
in message ...

I have been handed a VBA project which contains the following code (See
end). When I run the project I get the following error.

Encoutered a problem. Please ensure the column name has not been
amended. If the problem persists please report to administrator.


ErrorDes: Subscript out of range ErrorID:9 ErrorSource: VBAProject

*Can anybody please clarify why I am getting this error and how I
should resolve it. *


Dim sheeP As String

sheeP = ActiveSheet.Name

Dim dic1 As Object, x1, r1 As Range
Dim dic2 As Object, x2, r2 As Range
Dim p, q, s, fnlNumb As Variant
Set dic1 = CreateObject("Scripting.Dictionary")
Set dic2 = CreateObject("Scripting.Dictionary")

With Cells
Set c = .Find("Sell-to", LookIn:=xlValues, LookAt:=xlPart)
If Not c Is Nothing Then
firstAddress = c.AddressLocal
Do
add1 = c.AddressLocal

With Sheets(sheeP)
For Each r1 In .Range(add1)

p = Split(r1, " ")
q = Split(p(2), "(")
s = Split(q(1), ")")
fnlNumb = s(0)

If Not IsEmpty(r1) And Not dic1.exists(p(3) & " " & p(4) & " "
& fnlNumb) Then

dic1.Add p(3) & " " & p(4) & " " & fnlNumb, fnlNumb

End If
Next
End With

Set c = .FindNext(c)

Loop While Not c Is Nothing And c.AddressLocal < firstAddress

End If
End With


--
moglione1
------------------------------------------------------------------------
moglione1's Profile:
http://www.excelforum.com/member.php...o&userid=26414
View this thread: http://www.excelforum.com/showthread...hreadid=400374

The VBA debug highlights the following line:

s = Split(q(1), ")")


--
moglione1
------------------------------------------------------------------------
moglione1's Profile: http://www.excelforum.com/member.php...o&userid=26414
View this thread: http://www.excelforum.com/showthread...hreadid=400374