VBA Arrays

Hi Anthony,

Thanks for the above. A follow on question:

I have written some code based on the above and some other pointers I've found online:

/***

sub NewArrayTest()

Dim Worksheet As Variant
Dim wCohortEngine() As Worksheet
Dim wCohortResults(0) As Worksheet

Dim intTestArrayNumber As Integer
intTestArrayNumber = shtcohortresults.Range("H7").Value - shtcohortresults.Range("H6").Value

Dim intCurrentAge As Integer
intCurrentAge = 0

Set wCohortResults(0) = shtcohortresults

ReDim wCohortEngine(intTestArrayNumber)

For Each Worksheet In wCohortEngine

Set wCohortEngine(intCurrentAge) = shtcohortmodel

wCohortEngine(intCurrentAge).Range("c125").Value = 1 + intCurrentAge
'Debug.Print wCohortEngine.Range("c125").Value

wCohortResults(0).Range("d33:d40").Offset(0, intCurrentAge).Value = _
wCohortEngine(intCurrentAge).Range("d327:d334").Value

wCohortResults(0).Range("d49:d56").Offset(0, intCurrentAge).Value = _
wCohortEngine(intCurrentAge).Range("d335:d342").Value

'Debug.Print wCohortEngine.Range("d335").Value
'Debug.Print shtcohortresults.Range("d49").Value

intCurrentAge = intCurrentAge + 1

Next Worksheet

End Sub

**/

Currently, with testarraynumber=100 it takes approximately 15s to complete. I need to make this quicker as the next step for the analysis is to perform a monte carlo simulation based on the aggregated result of the code above.

My guess is that getting the worksheets in the array to update simultaneously instead of within a loop would be the quickest but I have no clue how to do this!

Anything you can suggest that may help would be awesome.

Thanks - Victor