# Subscript of our range

#### smartl50

##### New Member
Hi,

Im writing some code to calculate daily, monthly and annual realised volatlity. ( I know i can do this with stdev but for what i want to use it for i want a manual calc.)

I keep getting "subscript out of range" error once it goes beyond my last cell in the monthly calcluation

Any ideas why?
Code:
``````Sub RealisedVolbyDate1()

Dim cell As Variant
Dim cell2 As Variant
Dim cell3 As Variant
Dim count As Double
Dim rng As range
Dim rng2 As range
Dim rng3 As range
Dim formula As Double
Dim i As Long
Dim dte As Variant
Set rng = range("D7", range("D7").End(xlDown))
Set rng2 = range("D27", range("D27").End(xlDown))
Set rng3 = range("D252", range("D252").End(xlDown))
Set dte = range("G3")
cell = rng
cell2 = rng2
cell3 = rng3
For i = LBound(cell, 1) To UBound(cell, 1)
If dte = "Daily" And cell(i, 1) > 0 Then
rng(i, 3).formula = "=SQRT(SUM(RC[-1]/COUNT(RC[-1])))"

ElseIf dte = "Monthly" And cell2(i, 1) > 0 Then
rng2(i, 3).formula = "=SQRT(SUM(R[-21]C[-1]:R[0]C[-1])/COUNT(R[-21]C[-1]:R[0]C[-1])*12)"

ElseIf dte = "Annually" And cell3(i, 1) > 0 Then
rng3(i, 3).formula = "=SQRT(SUM(R[-251]C[-1]:R[0]C[-1])/COUNT(R[-251]C[-1]:R[0]C[-1])*252)"
End If
Next
End Sub``````

Last edited by a moderator:

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Kaper

##### Board Regular
Your rng is just 1 column wide, but you try to address rng(i,3)

think of such construction as:
Code:
``[COLOR=#333333]rng(i, 1).offset(0,2).formula = "=SQRT(SUM(RC[-1]/COUNT(RC[-1])))"[/COLOR]``

Another possibility is that your ranges rng, rng2 and rng3 have different number of cells (rows).
You loop from first to last row of rng (table cell) and once passes through last row of rng2 or rng3 - crashes.

PS. Note how readability is increased by using code tags to publish code

#### smartl50

##### New Member
Hi Kaper

Thanks for your suggestion but it doesnt resolve the issue

The subscript error is specifically coming from this line

ElseIf dte = "Monthly" And cell2(i, 1) > 0 Then

If i go through the code it calculates it for every cell but then gives this error after the last cell, if you hover over cell2(i,1) it displays the error.

Thanks

Lee

#### Kaper

##### Board Regular
Try

Code:
``````cell = rng[/COLOR]
[COLOR=#333333]cell2 = rng2[/COLOR]
[COLOR=#333333]cell3 = rng3
msgbox ubound(cell), [/COLOR][COLOR=#333333]ubound(cell2),[/COLOR][COLOR=#333333] [/COLOR][COLOR=#333333]ubound(cell3)[/COLOR]``````
and check if all 3 number4s are equal
if not - it's probably the second case I've been writing about previously

#### smartl50

##### New Member

Hi Kaper,

You are correct, when I add the message box it shows me the correct ranges of 545, 525 and 300 which is what I expect.

If i set my volatiity to Daily then this works fine and doesnt give the error, however, when I set it as monthly then it gives the error.

Im not quite sure what your suggestion is for the second case?

#### Kaper

##### Board Regular
so you do a loop going from i = 1 up to 545
as long as i<= 525 everything works fine, but when i hits 526 and dte is "Monthly"
you try read cell2(526, 1) . No way Cell2 table has only 525 elements.

Ok. It was diagnose part, now the treatment proposition:
Code:
``````cell3 = rng3
' here change starts
If dte = "Daily" then
For i = LBound(cell, 1) To UBound(cell, 1)
If cell(i, 1) > 0 Then
rng(i, 3).formula = "=SQRT(SUM(RC[-1]/COUNT(RC[-1])))"
end if
next i
ElseIf dte = "Monthly" then
For i = LBound(cell2, 1) To UBound(cell2, 1)
if cell2(i, 1) > 0 Then
rng2(i, 3).formula = "=SQRT(SUM(R[-21]C[-1]:R[0]C[-1])/COUNT(R[-21]C[-1]:R[0]C[-1])*12)"
end if
next i
ElseIf dte = "Annually" then
For i = LBound(cell3, 1) To UBound(cell3, 1)
if cell3(i, 1) > 0 Then
rng3(i, 3).formula = "=SQRT(SUM(R[-251]C[-1]:R[0]C[-1])/COUNT(R[-251]C[-1]:R[0]C[-1])*252)"
end if
Next i
End If
End Sub[COLOR=#333333]
[/COLOR]``````

Last edited:

#### smartl50

##### New Member

Kaper, that worked.. thank you so much for your help

#### smartl50

##### New Member
Kaper, Can i lean on your expertise again.

Ive added an additional condition to say that if a cell has a greater value than the previous cell then put the daily vol in one column otherwise the next column.

The easiest way I thought to do this was And cell(i, 1) > cell(i-1, 1) but cell(i-1,1) gives me again a subcript error but if i do cell(i+1,1) i works (although not what i want) do you know why this would be?

I tried to get around this by doing

If cell(i + 1, 1) > 0 And cell(i + 1, 1) > cell(i, 1) Then

but then i get a subscript error when i get past the bottom cell again

#### Kaper

##### Board Regular
But where shall go result for first row - the one, when there is no data in a row above?
As a general rule - you shall treat this first row as a special case. For instance for "Daily part":
Code:
``````cell3 = rng3
' here change starts
If dte = "Daily" then
If cell(LBound(cell, 1), 1) > 0 Then
rng(LBound(cell, 1), 3).formula = "=SQRT(SUM(RC[-1]/COUNT(RC[-1])))"
end if
For i = LBound(cell, 1)+1 To UBound(cell, 1)
If cell(i, 1) > 0 Then
if [COLOR=#574123]cell(i, 1) > cell(i-1, 1) then[/COLOR]
rng(i, 3).formula = "=SQRT(SUM(RC[-1]/COUNT(RC[-1])))"
else
rng(i, 4).formula = "=SQRT(SUM(RC[-1]/COUNT(RC[-1])))"
end if
end if
next i
'....``````

And a reminder - please use CODE tags. I reminded it in my first answer, and one of moderators edited your first post.
May be you do not feel it yourself (as a beginner in programming) but for those, who are for longer time "in business" it IS a big difference.

Replies
2
Views
86
Replies
6
Views
95
Replies
8
Views
77
Replies
12
Views
115
Replies
6
Views
39