VBA - price time series calcs on array - run time error 13

[msaldut]

<tbody>
[TD="class: votecell"] 0 down vote favorite
[/TD]
[TD="class: postcell"] I suspect my question has a very simple answer, and yet I cannot find an answer that I understand in searching all over the site.


Ticker1 = an array of stock prices (1 To 2542)
PctChg1 = an array which I hope will ultimately hold the results of (Price_last / Price_prev)-1
i = counter


For i = 2 To UBound(Ticker1)
PctChg1(i, 1) = Ticker1(i, 1) / Ticker1(i - 1, 1) - 1
Next i


...something about the bold part it does not like - I have fooled around with ranges as well and cannot make sense of this. I don't know why it thinks I am dividing by a number that isn't there - if i am starting at point "2" (i=2) of the array, why wouldn't I be able to reference point "1" ?


Thanks so much in advance for your help.


[/TD]

</tbody>

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

I have had to guess how you have dimensioned your arrays but I have just tried this and it works OK.

Sub xxxx()

    Dim Ticker1(2542, 1) As Double
    Dim PctChg1(2542, 1) As Double
    Dim i As Long
    
    For i = 1 To 2542
        Ticker1(i, 1) = 100
    Next
    
    For i = 2 To UBound(Ticker1)
        PctChg1(i, 1) = Ticker1(i, 1) / Ticker1(i - 1, 1) - 1
    Next i

    Debug.Print UBound(Ticker1)
    
End Sub

I think Error 13 is a Type mismatch so are both arrays numeric?