Problem with array in VBA - monte carlo simulation

[joejackson123]

All, I'm trying to do a montecarlo simulation, and have the following code:

Dim asd As Double
Dim bsd As Double
Dim csd As Double
Dim dsd As Double


Dim xx As Variant
Dim montecarloarray() As Variant


ReDim montecarloarray(1 To 1)


For xx = 1 To 100


asd = Rnd
bsd = Rnd
csd = Rnd
dsd = Rnd


Range("disc").Value = WorksheetFunction.Norm_Inv(asd, 1, 1.67)
Range("cof").Value = WorksheetFunction.Norm_Inv(bsd, 1, 1.67)
Range("syn").Value = WorksheetFunction.Norm_Inv(csd, 1, 1.67)
Range("cash").Value = WorksheetFunction.Norm_Inv(dsd, 1, 1.67)


montecarloarray(UBound(montecarloarray)) = Range("propoffer").Value
ReDim Preserve montecarloarray(1 To UBound(montecarloarray) + 1)


Next xx




Range("ar_2").Resize(UBound(montecarloarray)) = Application.Transpose(montecarloarray)




End Sub

I get a "subscript out of range error" on

montecarloarray(UBound(montecarloarray)) = Range("propoffer").Value

. How can I fix this? Any help is much appreciated, thank you!

 

[shg]

The error means that there is no named range propoffer in the scope of the active worksheet.