Error 9 Subscript out of range

[Jack888]

Hi Everyone,

I've been having problems with my VBA code. I'm currently running out of ideas of how to fix this problem. I kept on getting error 9 susbscript out of range.
Can someone please help. I'm new to VBA.

Dim Vol0_1, Vol1_1 As Double
Dim Vol0_2, Vol1_2 As Double
Dim Vol0_3, Vol1_3 As Double
Dim ZCB As Variant
ReDim F(0 To 1, 0 To nts) As Double
ReDim r(0 To nts) As Double
Dim rand1 As Double
Dim rand2 As Double
Dim rand3 As Double
Dim Tenor As Double
Dim dt As Double

dt = Range("B12").Value
Tenor = Range("B13").Value
T = Range("B14").Value
NI = Range("B15").Value
nts = T / dt Range("B7").Select

For i = 2 To 51
If T = Cells(7, i).Value Then
F(0, 0) = Cells(8, i).Value
D1 = Cells(2, i + 1).Value
Vol1_1 = Cells(3, i).Value
Vol1_2 = Cells(4, i).Value
Vol1_3 = Cells(5, i).Value
T1 = Cells(7, i).Value

F(1, 0) = Cells(8, i - 1).Value
D0 = Cells(2, i - 1).Value
Vol0_1 = Cells(3, i - 1).Value
Vol0_2 = Cells(4, i - 1).Value
Vol0_3 = Cells(5, i - 1).Value
T0 = Cells(7, i - 1).Value
End If
Next i

r(0) = F(1, 0)
s = 0
For i = 0 To NI

rand1 = Rnd + Rnd + Rnd + Rnd + Rnd + Rnd + Rnd + Rnd + Rnd + Rnd + Rnd + Rnd - 6
rand2 = Rnd + Rnd + Rnd + Rnd + Rnd + Rnd + Rnd + Rnd + Rnd + Rnd + Rnd + Rnd - 6
rand3 = Rnd + Rnd + Rnd + Rnd + Rnd + Rnd + Rnd + Rnd + Rnd + Rnd + Rnd + Rnd - 6

'The next loop is where i kept on getting the error message.
For k = 1 To nts
F(0, k) = F(0, k - 1) + D0 * dt + (Vol0_1 * rand1 + Vol0_2 * rand2 + Vol0_3 * rand3) * Sqr(dt) + ((F(0, k - 1) - F(1, k - 1)) / (T0 - T1)) / Tenor
F(1, k) = F(1, k - 1) + D0 * dt + (Vol0_1 * rand1 + Vol0_2 * rand2 + Vol0_3 * rand3) * Sqr(dt) + ((F(1, k - 1) - F(0, k - 1)) / (T1 - T0)) / Tenor
r(k) = F(1, k)
Next k
ZCB = Exp(-1 * WorksheetFunction.sum(r) * dt)
s = s + ZCB / NI
Next i

Range("B16").Value = s

End Sub

Any suggestion would be very much appreciated.

Thank you

 

[ukmikeb]

Hi

Welcome to the Forum

Assuming that you don't have any variable assignments before the code displayed, these statements -

ReDim F(0 To 1, 0 To nts) As Double
ReDim r(0 To nts) As Double

will only treat the value of nts as 0 so your arrays will only have a zero element.

Try placing those statements after the line where you set the value of the nts variable.

hth

 

[Jack888]

Sweet, Thank you Mike it worked well.

 

[ukmikeb]

Hi

Pleased to have helped solve your problem.

Thanks for the feedback.

Good luck with your project.