Subscript of our range

smartl50

New Member
Joined
Jul 9, 2018
Messages
5
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:

Some videos you may like

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
Joined
Mar 14, 2014
Messages
230
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
Joined
Jul 9, 2018
Messages
5
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
Joined
Mar 14, 2014
Messages
230
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
Joined
Jul 9, 2018
Messages
5

ADVERTISEMENT

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
Joined
Mar 14, 2014
Messages
230
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
Joined
Jul 9, 2018
Messages
5

ADVERTISEMENT

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

smartl50

New Member
Joined
Jul 9, 2018
Messages
5
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
Joined
Mar 14, 2014
Messages
230
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,275
Messages
5,527,721
Members
409,784
Latest member
AdamPriest

This Week's Hot Topics

Top