Can't debug this code. The Arrays are producing a Subscript out of Range error

I wrote the following code and it blocks the VBA procedure at the point where I define the Arrays and nb_Actions and nb_Cours. I tried changing Valeurs Action to Variant and others but it didn't work :/
It is a problem I have quite often.

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

Hello,

Just to start, nb_Cours and nb_Actions might have to be declared as Long variable type.

Second, I would suggest declaring your array variables in the Dims (as "Dim ValeursAction() as Double, Esperance() as Double") and then using:

Redim Preserve ValeursAction(1 to nb_Cours)
Redim Preserve Esperance(1 to N)

I think that should clear it up...


Cheers!
Xervice

Ok, I'll try

Your error portion seemed to work just fine for me. I agree that declaring them a Long would be better but integer should work. The only time I could duplicate a "subscript out of range" error was if column 2 either had no value or a value only in row one. This makes sense since you can't go (1 to 0). Below is what I did.

Sub test()
Dim nb_Cours As Integer
Dim nb_Actions As Integer
Dim iTest As Integer

nb_Cours = Worksheets("Actions").Cells(Rows.Count, 2).End(xlUp).Row - 1 'no error
     nb_Actions = Worksheets("Actions").Cells(1, Columns.Count).End(xlToLeft).Column - 1 'no error

ReDim ValeursAction(1 To nb_Cours) As Double 'no error

For iTest = LBound(ValeursAction) To UBound(ValeursAction)
    MsgBox (iTest)
Next iTest

End Sub

Would you mind having a look at it here: https://docs.google.com/file/d/0Bwjh...it?usp=sharing ?
It contains the actual project I am working with. I erased a few spreadsheets but the ones I have problems with are there

I applied your suggestions CrossService and I still get that error...

Could anyone at least give me a hint what is wrong in the file..? I could work around that.. now I just have no clue any longer.. I applied all previous suggestions :/

One thing I DON'T see is a sheet named "Classement" in your workbook, yet you're referencing it in your code. I've added it but that doesn't seem to solve the subscript out of range error. When i step through your code, I get the error after this line

ReDim Preserve Esperance(1 To N)

I just realized it too!! I actually marked Classement instead of Ponderation Sortino.. and N=4 should be placed BEFORE ReDim Preserve Esperance. It works now, except the values it returns in table ESPERANCE are zero for the first two :S

Thanks for the help!