Excel Error - Subscript out of Range

Hello Experts,

I changed the following formula from:


Addr = Array("AQ3", "AV3", "AP3", "AU3")    'Watch these cells for price changes
targ = Array(31, 31, 31, 31)                'Look for prices above these threshhold values
n = UBound(Stocks)
For i = 0 To n
    With Range(Addr(i))
        If Not IsError(.Value) Then
            If Stocks(i) <> .Value Then
                Stocks(i) = .Value
                If .Value > targ(i) Then
                    Open "C:\Users\User\Documents\ABC.txt" For Append As #1  'Change path & name to suit
                    Write #1, .Address(False, False), .Value, Date, Format(Time, "hh:mm:ss.ss")
                    Close 1
                End If
            End If
        End If
    End With
Next
End Sub

To:

Addr = Array("AQ3", "AV3")    'Watch these cells for price changes
targ = Array(31, 31)                'Look for prices above these threshhold values
n = UBound(Stocks)
For i = 0 To n
    With Range(Addr(i))
        If Not IsError(.Value) Then
            If Stocks(i) <> .Value Then
                Stocks(i) = .Value
                If .Value > targ(i) Then
                    Open "C:\Users\User\Documents\ABC.txt" For Append As #1  'Change path & name to suit
                    Write #1, .Address(False, False), .Value, Date, Format(Time, "hh:mm:ss.ss")
                    Close 1
                End If
            End If
        End If
    End With
Next
End Sub

And now I get the error message: Suscript out of Range, and then it highlights With Range(Addr(i))

Basically, I changed:

Addr = Array("AQ3", "AV3", "AP3", "AU3")    'Watch these cells for price changes
targ = Array(31, 31, 31, 31)                'Look for prices above these threshhold values

To

Addr = Array("AQ3", "AV3")    'Watch these cells for price changes
targ = Array(31, 31)                'Look for prices above these threshhold values


If you need the spreadsheet to help resolve this issue please let me know.


Cheers

Carlton
cpatte7372Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
NorieConnect With a Mentor VBA ExpertCommented:
You can't go through 2 arrays which have different dimensions with the same loop.

If you use the upper bound of the larger array for the loop, eg Stocks, you'll get the error.

If you use the smaller array then you'll miss values in the larger array.

You probably need another loop, perhaps something like this.
 For j = LBound(Addr) To UBound(Addr)

        For i = LBound(Stocks) To UBound(Stocks)

            With Range(Addr(j))

                If Not IsError(.Value) Then
                    If Stocks(i) <> .Value Then
                        Stocks(i) = .Value

                        If .Value > targ(i) Then

                            Open "C:\Users\User\Documents\ABC.txt" For Append As #1  'Change path & name to suit

                            Write #1, .Address(False, False), .Value, Date, Format(Time, "hh:mm:ss.ss")

                            Close 1

                        End If
                    End If
                End If
            End With

        Next i
    Next j

Open in new window

0
 
Patrick MatthewsCommented:
Before your line:

n = UBound(Stocks)

Open in new window


please add the following:

If UBound(Stocks) > UBound(Addr) Then
    MsgBox "You are guaranteed to get a subscript out of range", vbCritical, "Danger Will Robinson!"
End If

Open in new window


All kidding aside, looks to me like Stocks simply has a higher upper bound than Addr, which would definitely cause that error.
0
 
NorieVBA ExpertCommented:
Try this for the loop.
For I = LBound(Addr) To UBound(Addr)

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SANTABABYCommented:
The arrays that you have defined starts at Index 1.
Change yor loop variable's startting value to 1. i.e.
Replace
For i = 0 To n
with
For i = 1 To n

Use do something, so that you never reference to index 0 position of your arrays.
0
 
Patrick MatthewsCommented:
SANTABABY,

>>The arrays that you have defined starts at Index 1.

Please indicate where you see that in the code.

We do not see where the Stocks array is defined, so we have no idea about that, but the arrays defined by the Array() function are DEFINITELY starting at 0, regardless of whether Option Base 1 is being used or not.

Patrick
0
 
StephenJRCommented:
SANTABABY - I don't think that is the issue, as has been addressed by the two prior posts.
0
 
cpatte7372Author Commented:
Santababy,

Thanks for responding. I applied the changes as you suggested, but I still get same error message. I have attached the spreadsheet if that might help resolve this issue.

Cheers mate.
EE-SubscripError.xlsm
0
 
SANTABABYCommented:
I mixed up with another solution. My bad !!

0
 
Patrick MatthewsCommented:
So, as I suspected...

In the Sheet2 Calculate event sub, Stocks is defined here:

Static Stocks(3) As Double      'Starts with element 0

Open in new window


Addr is populated here:

Addr = Array("AQ3", "AV3")    'Watch these cells for price changes

Open in new window


That sets the lower bound at 0 for Addr, and the upper bound at 1.

You set n, the counter for the For...Next, here:

n = UBound(Stocks)

Open in new window


Thus, n = 3.

However, once you get to i = 2 in the For...Next loop, this line throws your subscript out of range error:

    With Range(Addr(i))

Open in new window

0
 
NorieVBA ExpertCommented:
Stocks is 0 To 3.

Addr is 0 To 1

If you use UBound(Stocks) for the loop you will be going from 0 To 3.

There is no Addr(2) or Addr(3).
0
 
SANTABABYCommented:
The bounds are not matching between stocks and Addr(from what I see in the code).
They should have same dimension.
0
 
cpatte7372Author Commented:
Gentlemen,

I'm a little confused as to what the solution is?

Cheers
0
 
Patrick MatthewsCommented:
Simple, really.  Your arrays have different upper bounds.

Did you read the last few Expert Comments?
0
 
cpatte7372Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.