Solved

Excel Error - Subscript out of Range

Posted on 2011-09-28
14
368 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

705 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

18 Experts available now in Live!

Get 1:1 Help Now