Subscript of our range

[smartl50]

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?

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

 

[Kaper]

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

think of such construction as:

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

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]

Try

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]

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]

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:

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]

 

[smartl50]

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

 

[smartl50]

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]

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

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.