use looping to replicate data table functionality

[kublakhan]

I've created a cash flow model that utilizes circular references and iterations. It seems to work okay. When I add data tables, however, the added iterations create #NA errors. I need those data tables so I've tried to replicate what I need in VBA. I'd like some help in looping it. Basically, I have five scenarios and I want to insert each one and record the results in a table. Here's my brute force version that cycles through scenarios 1 & 2.
[vba]Sub RunMultScenarios()
Application.ScreenUpdating = False

'A better programmer could/would loop this routine
'This is brute force

'Run _first_ data table scenario through model
Range("Scen_Curr").Value = Worksheets("Scenarios").Range("AE111").Value

'Calculate the model to eliminate potential #NAs or non-balancing sums
Call SuperCalc

'Move results to data table
''Trading
Worksheets("Scenarios").Range("AI111").Value = Worksheets("Summary").Range("K70").Value
Worksheets("Scenarios").Range("AJ111").Value = Worksheets("Summary").Range("L70").Value
Worksheets("Scenarios").Range("AK111").Value = Worksheets("Summary").Range("M70").Value
Worksheets("Scenarios").Range("AL111").Value = Worksheets("Summary").Range("N70").Value
Worksheets("Scenarios").Range("AM111").Value = Worksheets("Summary").Range("O70").Value
''DCF
Worksheets("Scenarios").Range("AP111").Value = Worksheets("Summary").Range("K71").Value
Worksheets("Scenarios").Range("AQ111").Value = Worksheets("Summary").Range("L71").Value
Worksheets("Scenarios").Range("AR111").Value = Worksheets("Summary").Range("M71").Value
Worksheets("Scenarios").Range("AS111").Value = Worksheets("Summary").Range("N71").Value
Worksheets("Scenarios").Range("AT111").Value = Worksheets("Summary").Range("O71").Value
'''LBO
Worksheets("Scenarios").Range("AW111").Value = Worksheets("Summary").Range("K72").Value
Worksheets("Scenarios").Range("AX111").Value = Worksheets("Summary").Range("L72").Value
Worksheets("Scenarios").Range("AY111").Value = Worksheets("Summary").Range("M72").Value
Worksheets("Scenarios").Range("AZ111").Value = Worksheets("Summary").Range("N72").Value
Worksheets("Scenarios").Range("BA111").Value = Worksheets("Summary").Range("O72").Value

'Run _second_ data table scenario through model
Range("Scen_Curr").Value = Worksheets("Scenarios").Range("AE112").Value

'Calculate the model to eliminate potential #NAs or non-balancing sums
Call SuperCalc

'Move results to data table
''Trading
Worksheets("Scenarios").Range("AI112").Value = Worksheets("Summary").Range("K70").Value
Worksheets("Scenarios").Range("AJ112").Value = Worksheets("Summary").Range("L70").Value
Worksheets("Scenarios").Range("AK112").Value = Worksheets("Summary").Range("M70").Value
Worksheets("Scenarios").Range("AL112").Value = Worksheets("Summary").Range("N70").Value
Worksheets("Scenarios").Range("AM112").Value = Worksheets("Summary").Range("O70").Value
''DCF
Worksheets("Scenarios").Range("AP112").Value = Worksheets("Summary").Range("K71").Value
Worksheets("Scenarios").Range("AQ112").Value = Worksheets("Summary").Range("L71").Value
Worksheets("Scenarios").Range("AR112").Value = Worksheets("Summary").Range("M71").Value
Worksheets("Scenarios").Range("AS112").Value = Worksheets("Summary").Range("N71").Value
Worksheets("Scenarios").Range("AT112").Value = Worksheets("Summary").Range("O71").Value
'''LBO
Worksheets("Scenarios").Range("AW112").Value = Worksheets("Summary").Range("K72").Value
Worksheets("Scenarios").Range("AX112").Value = Worksheets("Summary").Range("L72").Value
Worksheets("Scenarios").Range("AY112").Value = Worksheets("Summary").Range("M72").Value
Worksheets("Scenarios").Range("AZ112").Value = Worksheets("Summary").Range("N72").Value
Worksheets("Scenarios").Range("BA112").Value = Worksheets("Summary").Range("O72").Value

Application.ScreenUpdating = True
End Sub
[/vba]

[mdmackillop]

Here's a looping example using variables to refer to the sheets
[VBA]Option Explicit
Sub Looping()

Dim Source As Worksheet
Dim Tgt As Worksheet
Dim i As Long

Set Source = Worksheets("Summary")
Set Tgt = Worksheets("Scenarios")

''Trading
For i = 0 To 4
Tgt.Range("AI111").Offset(, i).Value = Source.Range("K70").Offset(, i).Value
Next

End Sub
[/VBA]

[kublakhan]

Thank you, that helps. Here's the final code:
[VBA]Sub RunMultScenarios()
Application.ScreenUpdating = False

Dim Source As Worksheet
Dim Target As Worksheet
Dim i As Long
Dim j As Long

Set Source = Worksheets("Summary")
Set Target = Worksheets("Scenarios")

'Loop scenarios through model
For i = 0 To 4
Range("Scen_Curr").Value = Target.Range("AE111").Offset(i, 0).Value

'Calculate the model
Call SuperCalc

'Move results to data table
For j = 0 To 4
''Error handling for bad or empty scenarios
If Range("SuperCheck").Errors.Item(xlEvaluateToError).Value = True Then
Target.Range("AI111").Offset(i, j).Value = 0
Target.Range("AI122").Offset(i, j).Value = 0
Target.Range("AP111").Offset(i, j).Value = 0
Target.Range("AP122").Offset(i, j).Value = 0
Target.Range("AW111").Offset(i, j).Value = 0
Target.Range("AW122").Offset(i, j).Value = 0
Else
''Trading
Target.Range("AI111").Offset(i, j).Value = Source.Range("K70").Offset(0, j).Value
Target.Range("AI122").Offset(i, j).Value = Source.Range("K75").Offset(0, j).Value
''DCF
Target.Range("AP111").Offset(i, j).Value = Source.Range("K71").Offset(0, j).Value
Target.Range("AP122").Offset(i, j).Value = Source.Range("K76").Offset(0, j).Value
''LBO
Target.Range("AW111").Offset(i, j).Value = Source.Range("K72").Offset(0, j).Value
Target.Range("AW122").Offset(i, j).Value = Source.Range("K77").Offset(0, j).Value
End If
Next j

Next i

'Reset model to first scenario
Range("Scen_Curr").Value = Target.Range("AE111").Value
Call SuperCalc

Application.ScreenUpdating = True
End Sub[/VBA]