Solved

Excel Error - Subscript out of Range

Posted on 2011-09-28
14
380 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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
 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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 how to use longer labels with horizontal bar charts instead of the vertical column chart.

813 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

11 Experts available now in Live!

Get 1:1 Help Now