Filling an Array in VBA

Hi,

I am working on a simulation and am struggling with filling an array.
Some background
* As first step I made a simulation of 1000 loans
* As a next step I have made a calculation sheet (Sim_bond_calc) to
calculate cash flows for each mortgage (the number of cash flows is
variable (parameter ‘i’ in my macro) / max 121 rows per loan). The
sheet uses the simulated loans as a starting point (parameter ‘h’ in
my macro)
* Now I am trying to create a macro that performs the cash flow
calculation for each simulated loan and stores the output in separate
tab. Since the number of rows may grow to approximately 121.000 I have
chosen to use an array function.

I am struggling with filling the array properly:
• First I have tried to loop through the calculation sheet and write
the results to the array. This is shown in Macro 1 below. The macro
does only store the results of the last loan (number 1000)
• Next I have tried to copy all cash flows from the calculation sheet
to the array (macro 2) but whatever I try I get a run-time ‘1004’ –
Method ‘Range of object’_Global’ failed error.

I hope someone can help me!

Kind regards,

Paul


MACRO 1

Sub Sim_Cash_Flow_Projection()
Dim CellsDown As Long, CellsAcross As Integer
Dim h As Long, i As Long, j As Integer
Dim TempArray() As Variant
Dim TheRange As Range
Dim CurVal As Variant
Dim Row_ As Integer
Dim Cnt As Integer
Dim CurVal1 As Integer
Dim RowCF As Integer


'Clear previous results
Application.Goto Reference:="SIm_Bond_Range"
Selection.ClearContents

' Get dimensions
CellsDown = Sheets("Sim_loan_DB").Range("B2").Value * 121
CellsAcross = Sheets("Sim_loan_DB").Range("B4").Value

RowCF = Sheets("Sim_loan_DB").Range("B6").Value

' Redimension temporary array
ReDim TempArray(0 To CellsDown, 0 To CellsAcross)

' set worksheet range
Set TheRange = Range(Cells(2, 1), Cells(CellsDown, CellsAcross))

' Fill the temporary array

Application.ScreenUpdating = False


Sheets("Sim_bond_calc").Select
CurVal = 26
CurVal1 = 1

For h = 1 To Sheets("Sim_loan_DB").Range("B2")
Sheets("Sim_bond_calc").Range("c10").Value = h
For i = 0 To Sheets("Sim_bond_calc").Range("c18")
For j = 0 To CellsAcross
TempArray(i, j) = Cells(CurVal + i, CurVal1 + j).Value
Next j
Next i
Next h

' Transfer temporary array to worksheet
TheRange.Value = TempArray

Application.ScreenUpdating = True

End Sub

MACRO 2
Sub Sim_Cash_Flow_Projection2()
Dim CellsDown As Long, CellsAcross As Integer
Dim h As Long, i As Long, j As Integer
Dim TempArray() As Variant
Dim TheRange As Range
Dim CurVal As Variant
Dim Row_ As Integer
Dim Cnt As Integer
Dim CurVal1 As Integer
Dim RowCF As Integer


'Clear previous results
Application.Goto Reference:="SIm_Bond_Range"
Selection.ClearContents

' Get dimensions
CellsDown = Sheets("Sim_loan_DB").Range("B2").Value * 121
CellsAcross = Sheets("Sim_loan_DB").Range("B4").Value

RowCF = Sheets("Sim_loan_DB").Range("B6").Value

' Redimension temporary array
ReDim TempArray(0 To CellsDown, 0 To CellsAcross)

' set worksheet range
Set TheRange = Range(Cells(2, 1), Cells(CellsDown, CellsAcross))

' Fill the temporary array

Application.ScreenUpdating = False


Sheets("Sim_bond_calc").Select

For h = 1 To Sheets("Sim_loan_DB").Range("B2")
Sheets("Sim_bond_calc").Range("c10").Value = h
TempArray() = Range(Cells(26,
1).Resize(Sheets("Sim_bond_calc").Range("c18"), 13)).Value
Next h

' Transfer temporary array to worksheet
TheRange.Value = TempArray

Application.ScreenUpdating = True

End Sub