#### jjewels119

##### New Member

- Joined
- Oct 27, 2011

- Messages
- 17

So I do my array for one year then I tried to create a loop that would allow me to do the same thing for multiple years. When I paste in my code I get the headers in A1 and B1. I get 4 zeros in range(a2:b3), then "N/A" in range (a4:b10). I tried transposing the array and had the same result. Here is my code:

Code:

```
Sub generateARRAY()
Dim NormalMU As Integer, NormalSIGMA As Integer
Dim mu As Double, sigma As Double, pi As Double
Dim firstYR As Integer, lastYR As Integer
Dim NumYrs As Integer
Dim cars As Integer, claims As Integer
Dim percent As Double
Dim U As Double, v As Double, y As Double
Dim X As Integer
Dim K As Integer
Dim ClaimGen() As Single
Dim AccidentYrs() As Single
Dim TotalClaim() As Single
'Declare Starting year, Ending year, number of cars, and percent
firstYR = 1970
lastYR = 1972
cars = 50
percent = 0.13
NumYrs = lastYR - firstYR + 1
K = 1
X = 0
'Declare Normal mu and sigma
NormalMU = 2000
NormalSIGMA = 3000
mu = Application.WorksheetFunction.Ln(NormalMU) - 0.5 * Application.WorksheetFunction.Ln(1 + (NormalSIGMA / NormalMU) ^ 2)
sigma = Sqr(Application.WorksheetFunction.Ln(1 + (NormalSIGMA / NormalMU) ^ 2))
pi = Application.WorksheetFunction.pi()
'Create an array with Claim dates & Claim Payments
Do
Sheets("sheet1").Select
claims = cars * (Exp(-percent) * (percent ^ K)) / (Application.WorksheetFunction.Fact(K))
ReDim ClaimGen(1 To 2, i = 1 To claims) As Single
For i = 1 To claims
U = Rnd
v = Rnd
y = Sqr(-2 * pi * Application.WorksheetFunction.Ln(U)) * Sin(2 * pi * v)
ClaimGen(1, i + X) = K + firstYR - 1
ClaimGen(2, i + X) = Round(Exp(mu + sigma * y), 2)
Next i
X = X + i
K = K + 1
Loop Until K + firstYR - 2 = lastYR
'Formats Payment column and puts the array in Excel
With Sheets("sheet1")
.Range("A2:B" & X + 1).Value = ClaimGen()
.Range("a1") = "Claim Year"
.Range("b1") = "Payment"
End With
Columns("b:b").NumberFormat = "0.00"
End Sub
```