Solved

Excel Error - Subscript out of Range

Posted on 2011-09-28
14
378 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:cpatte7372
  • 4
  • 3
  • 3
  • +2
14 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36720509
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
 
LVL 33

Expert Comment

by:Norie
ID: 36720511
Try this for the loop.
For I = LBound(Addr) To UBound(Addr)

Open in new window

0
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36720521
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36720548
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
 
LVL 24

Expert Comment

by:StephenJR
ID: 36720558
SANTABABY - I don't think that is the issue, as has been addressed by the two prior posts.
0
 

Author Comment

by:cpatte7372
ID: 36720561
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
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36720588
I mixed up with another solution. My bad !!

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36720594
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
 
LVL 33

Expert Comment

by:Norie
ID: 36720597
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
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36720606
The bounds are not matching between stocks and Addr(from what I see in the code).
They should have same dimension.
0
 

Author Comment

by:cpatte7372
ID: 36746860
Gentlemen,

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

Cheers
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36751020
Simple, really.  Your arrays have different upper bounds.

Did you read the last few Expert Comments?
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 36753161
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
 

Author Closing Comment

by:cpatte7372
ID: 36980427
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now