Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Excel Error - Subscript out of Range

Posted on 2011-09-28
Medium Priority
389 Views
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
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
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
Question by:cpatte7372
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3
• 3
• +2

LVL 93

Expert Comment

ID: 36720509

``````n = UBound(Stocks)
``````

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

All kidding aside, looks to me like Stocks simply has a higher upper bound than Addr, which would definitely cause that error.
0

LVL 34

Expert Comment

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

LVL 10

Expert Comment

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 93

Expert Comment

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

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

Author Comment

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

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

0

LVL 93

Expert Comment

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

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

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

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))
``````
0

LVL 34

Expert Comment

ID: 36720597
Stocks is 0 To 3.

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

0

LVL 10

Expert Comment

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

ID: 36746860
Gentlemen,

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

Cheers
0

LVL 93

Expert Comment

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

0

LVL 34

Accepted Solution

Norie earned 2000 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)

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

Author Closing Comment

ID: 36980427
Thanks
0

## Featured Post

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst youâ€™ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to dâ€¦
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month8 days, 7 hours left to enroll