troubleshooting Question

Excel VBA Array Out Of Bounds

Avatar of talltk
talltk asked on
Visual Basic ClassicMicrosoft Excel
11 Comments3 Solutions1237 ViewsLast Modified:
Topic: Excel VBA Programming
OS   : Windows XP Platform

Hi to all.  Rather new to this site so please bear with me.  

To make things simple, I have 2 tables in EXCEL, one called "IN" and one called "OUT".
Table "IN" has unique document numbers (meaning only one instance of each may be found in the column) which I store in an array called  documentNumberIn(200).  Table "OUT" has many instances of each document number from table "IN" which I also store in an array called  documentNumberOut(200).

I need to compare the two arrays to check for equal values AND also at the same time (this is the important part) check to see if there are any previous instances of the same document number from the OUT table.  If there is at least one instance from the previous record, my program will carry out steps ABC; if there isn't an instance from the previous record, my program will carry out steps DEF.  Hope you get the picture so far.

I initialize 2 for loops with variable 'i' to control the IN array and 'j' to control the OUT array.  
The following code receives the "subscript <out of range>" error because documentNumberOut(j-1) at the very first instance when j=0, causes the array to go out of bounds.

For i To 200
    For j To 200
        If ((documentNumberIn(i) = documentNumberOut(j)) And (documentNumberOut(j) = documentNumberOut(j-1)))

            'Carry out steps ABC here

        ElseIf ((documentNumberIn(i) = documentNumberOut(j)) And (documentNumberOut(j)<>documentNumberOut(j-1)))

            'Carry out steps DEF here

Can someone please help me if you know the answer?  Is there any array function for EXCEL VBA that tests if the array is out of bounds?  Thanks a million!!!!!  God bless you all!
Join our community to see this answer!
Unlock 3 Answers and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros