Loop through multiple rows per iteration with Excel VBA

[sjedi]

How can I convert my Excel VBA code, which currently loops by each row, to loop through 10,000 sets, with each set containing 20 rows? I understand that the Step function might work, but I can't figure out how to amend my code to enable this. Basically, I would like to "paste" an array of 20 rows (by 21 columns) via the following lines of code:

        .Range("CASHPASTE").Value = Application.Index(vInput1, r, 0)
        .Range("EQPASTE").Value = Application.Index(vInput2, r, 0)
        .Range("FIPASTE").Value = Application.Index(vInput3, r, 0)

And then "paste" the next 20 rows, and the following 20 rows and so forth.
Hope someone can help!

Full VBA code is shown below:

Option Explicit

Sub Calc()

Dim r As Long, NoRows As Long, NoTrials As Long, NoPeriods As Long
Dim vInput1 As Variant, vInput2 As Variant, vInput3 As Variant
Dim vIRR As Variant, vExitIRR As Variant

With Worksheets("MCInput")
        NoRows = .Cells(.Rows.Count, "B").End(xlUp).Row - .Range("TCASHRTN").Row + 1
        
        NoTrials = WorksheetFunction.Max(Range("C:C")) 'Number of Trials
        NoPeriods = WorksheetFunction.Max(Range("1:1")) - 1 'Number of Periods
        vInput1 = .Range("TCASHRTN").Resize(NoRows).Value
        vInput2 = .Range("TEQRTN").Resize(NoRows).Value
        vInput3 = .Range("TFIRTN").Resize(NoRows).Value
End With

MsgBox NoTrials & " Trials over " & NoPeriods & " Periods" & " Rows = " & NoRows

ReDim vExitIRR(1 To NoTrials)

With Worksheets("Calcs")

    For r = 1 To NoRows Step 20
        .Range("CASHPASTE").Value = Application.Index(vInput1, r, 0)
        .Range("EQPASTE").Value = Application.Index(vInput2, r, 0)
        .Range("FIPASTE").Value = Application.Index(vInput3, r, 0)
        
        vIRR = .Range("IRR").Value 'IRR is a single cell

        vExitIRR(r, 1) = vIRR

    Next r
End With

Worksheets("IRR").Range("B2").Resize(NoTrials, 1).Value = vExitIRR

End Sub

 

[StephenCrump]

One way you could do this:

- Define TCASHRTN, TEQRTN, TFIRTN in Excel as the entire column of data (which you could do dynamically) - presumably these have 200,000 rows (being 10,000 sets of 20)?
- Have a cell defined N, for the Nth iteration
- Have fomulae in your calculation area to pick up the 20 data points INDEX(TCASHRTN,20*N -19), INDEX(TCASHRTN,20*N -18) ..., INDEX(TCASHRTN,20*N) and similar for TEQRTN and TFIRTN. (Or maybe each dataset is 20 rows x 7 columns, 21 columns total)?
- Have VBA loop For i = 1 to 10,000 (= DataRows /20), setting the cell N to i
- Inside the loop, collect the IRRs as you are doing now - presumably NoTrials is also returning 10,000?

Also use Application.ScreenUpdating = False and turn on at the end.

 

[sjedi]

One way you could do this:

- Define TCASHRTN, TEQRTN, TFIRTN in Excel as the entire column of data (which you could do dynamically) - presumably these have 200,000 rows (being 10,000 sets of 20)?
- Have a cell defined N, for the Nth iteration
- Have fomulae in your calculation area to pick up the 20 data points INDEX(TCASHRTN,20*N -19), INDEX(TCASHRTN,20*N -18) ..., INDEX(TCASHRTN,20*N) and similar for TEQRTN and TFIRTN. (Or maybe each dataset is 20 rows x 7 columns, 21 columns total)?
- Have VBA loop For i = 1 to 10,000 (= DataRows /20), setting the cell N to i
- Inside the loop, collect the IRRs as you are doing now - presumably NoTrials is also returning 10,000?

Also use Application.ScreenUpdating = False and turn on at the end.

@StephenCrump thanks for the tip! Based on your suggestion, I'm guessing that I should amend my loop code to:

With Worksheets("Calcs")
    For i = 1 To NoRows/20
        .Range("N").Value = i
        vIRR = .Range("IRR").Value 'IRR is a single cell
        vExitIRR(i, 1) = vIRR
    Next i
End With

To clarify, TCASHRTN, TEQRTN and TFIRTN are named ranges in Excel that each have 200,000 rows (being 10,000 sets of 20 rows) and 21 columns. NoTrials = 10,000 and NoPeriods = 21 and NoRows = 200,000. I am intending to fetch data from TCASHRTN, TEQRTN and TFIRTN, 20 rows at the time for each data source, to input into the named ranges CASHPASTE, EQPASTE and FIPASTE respectively. Each of the named ranges CASHPASTE, EQPASTE and FIPASTE have 20 rows by 21 columns, so it will be able to take in data from each of the 3 data sources.

 

[StephenCrump]

Yes, simple code like this is what I had in mind.

I would probably add a helper column where the first value was 20*N-19, and subsequent rows +1. That way, all your INDEX() formula can use the helper column rather than recalculating the same row numbers based on N.

This approach is one way to minimise reading/writing between Excel/VBA which is usually the slow part of this sort of exercise. Make sure you turn .ScreenUpdating off, as this will also slow the process.

 

[sjedi]

Yes, simple code like this is what I had in mind.

I would probably add a helper column where the first value was 20*N-19, and subsequent rows +1. That way, all your INDEX() formula can use the helper column rather than recalculating the same row numbers based on N.

This approach is one way to minimise reading/writing between Excel/VBA which is usually the slow part of this sort of exercise. Make sure you turn .ScreenUpdating off, as this will also slow the process.

Thanks @StephenCrump

Yup, I have set Application.ScreenUpdating = FALSE, and added the Helper column. However, I experienced a Run-time error '1004' Application-defined or object defined error at

.Range("N").Value = i

Is this because my named cell "N" is on a different sheet from "Calcs"? Could I have set the dimension for i wrongly? I had set dimension for i, as

Dim i As Long

 

[StephenCrump]

Is this because my named cell "N" is on a different sheet from "Calcs"?

Yes. Assuming N is workbook scope, you need:

Range("N").Value = i

For
.Range("N").Value = i
to work, N will need to be scope Worksheets("Calcs"), or have workbook scope and be located on Worksheets("Calcs").

 

[sjedi]

Yes. Assuming N is workbook scope, you need:

Range("N").Value = i

For
.Range("N").Value = i
to work, N will need to be scope Worksheets("Calcs"), or have workbook scope and be located on Worksheets("Calcs").

Thanks @StephenCrump the code works now! Really appreciate your help on this

 

[StephenCrump]

Great, I'm glad you got it working.