Subscript out of range error in a sub with arrays

[seigna]

Hi everyone,

I am working on the following code and I am getting without stop this funny error: Subscript out of range. I have no clue how to fix it. It seems I get it each time I try working with arrays... It is pretty upsetting since I get it really almost EACH time.

Could someone please help me fix it?

Sub MatriceVarCov()
    Dim i As Integer, j As Integer, k As Integer
    Dim Valeurs As Integer
    Dim SomCov As Double
    Dim N As Integer
    Dim CoursActionbis As Range
    Dim nb_Cours As Integer
    Dim nb_Actions As Integer
    Dim Indice As Integer
    Dim Rendement As Double
    Dim Rendement2 As Double
    Dim Cova As Double
 


    nb_Cours = Worksheets("Actions").Cells(Rows.Count, 2).End(xlUp).Row - 1 'THIS LINE ERROR
     nb_Actions = Worksheets("Actions").Cells(1, Columns.Count).End(xlToLeft).Column - 1 'THIS LINE ERROR
    
    
    ReDim ValeursAction(1 To nb_Cours) As Double 'THIS LINE ERROR
    ReDim Esperance(1 To N) As Double 'THIS LINE ERROR
    'N = 4
            For i = 2 To N 
                Indice = Sheets("Classement").Cells((nb_Actions + 7 + (i - 2)), 7).Value
                Esperance(i) = Sheets("Analyse Statistique").Cells(Indice + 1, 2) 




            With Sheets("Ponderation Sortino")
                .Cells(4, 11 - i) = Esperance(i)
            End With


    
    For j = i To N
    
 
        SomCov = 0
    
        
        For k = 2 To nb_Cours
            ' Recuperation des rendements à partir du tableau calculé et affiché dans la sub rend
            Rendement = Sheets("Ponderation Sortino").Cells(5 + k, i)
            Rendement2 = Sheets("Ponderation Sortino").Cells(5 + k, j)
            SomCov = SomCov + (Rendement - Esperance(i)) * ((Rendement2) - (Esperance(j)))
            Cova = (1 / nb_Cours) * SomCov
        Next k
        
      
        With Sheets("Ponderation Sortino")
        .Cells(i + 5, j + 9) = Cova
        End With
    Next j


Next i
End Sub

 

[seigna]

You're right! It is supposed to be Ponderation Sortino instead of Classement.

 

[seigna]

One more thing, I don't know if you can see, but I also obtain Zeros for where I have the table with ESPERANCE... and I am not sure why..

 

[seigna]

Anyway, I will figure that out. Thank you so much for the help!

 

[p45cal]

First time round:
Indice = Sheets("Ponderation Sortino").Cells((nb_Actions + 7 + (i - 2)), 7).Value

is G17's value

Next time round (i has been incremented), it is G18's value, blank, so Indice is 0

I suspect Cells((nb_Actions + 7 + (i - 2)), 7) is not doing quite what you want.

 

[seigna]

First time round:
Indice = Sheets("Ponderation Sortino").Cells((nb_Actions + 7 + (i - 2)), 7).Value

is G17's value

Next time round (i has been incremented), it is G18's value, blank, so Indice is 0

I suspect Cells((nb_Actions + 7 + (i - 2)), 7) is not doing quite what you want.

So How can I make it go UP? instead of down..? I need it to start at the last one and go up..

 

[seigna]

I fixed it! I placed a minus instead of a + and brackets for (nb_Actions +7) Indice = Sheets("Ponderation Sortino").Cells((nb_Actions + 7 - (i - 2)), 7).Value

Thanks for the hint!

 

[p45cal]

So as i goes up in value you want the row number to go down. So -i must definitely be in there. This is just a guess:
Indice = Sheets("Ponderation Sortino").Cells(nb_Actions + 9 - i, 7).Value