Excel VBA loop through 10,000 sets of rows, each set containing 20 rows

Question

How can I convert my Excel VBA code, which currently loops by row, to loop through sets of 20 rows?

I understand that the Step function might work together with the following line .Range("V1").Value = Application.Index(vInput1, r, 0). However, I am unsure of how to amend my code to loop through each batch of 20 rows and shift down by 20 rows to loop through the next set of 20 rows.

Note: after 'pasting' each set of 20 rows into .range(V1), Excel calculations are used to generate the output for Price. I intend to generate Price for each set of 20 rows and I have a total of 10,000 sets (or trials)

Option Explicit

Sub Calc()

Dim r As Long, NoRows As Long, NoTrials As Long, NoPeriods As Long
Dim vInput1 As Variant
Dim vPrice As Variant, vCoPrice As Variant

With Worksheets("Input")
        NoRows = .Cells(.Rows.Count, "B").End(xlUp).Row - .Range("TINPUT").Row + 1

        NoTrials = WorksheetFunction.Max(Range("C:C")) 'Number of Trials
        NoPeriods = WorksheetFunction.Max(Range("1:1")) - 1 'Number of Periods
        vInput1 = .Range("TINPUT").Resize(NoRows).Value

End With

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

ReDim vCoPrice(1 To NoTrials)

With Worksheets("Calcs")

    For r = 1 To NoRows Step 20
        .Range("V1").Value = Application.Index(vInput1, r, 0)

vPrice = .Range("Price").Value 'Price is a single cell

        vCoPrice(r, 1) = vPrice

    Next r

End With

Worksheets("Price").Range("B2").Resize(NoTrials, 1).Value = vCoPrice

End Sub

A sample of my trials, trials 1 and 2, is shown below. Each trial consists of 20 rows (Grade 1 to 20) and 21 columns (Year 1 to 21). In total, I have 10,000 trials to loop through


+-------+-------+-------+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
| Grade | Fruit | Trial | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 6 | Year 7 | Year 8 | Year 9 | Year 10 | Year 11 | Year 12 | Year 13 | Year 14 | Year 15 | Year 16 | Year 17 | Year 18 | Year 19 | Year 20 | Year 21 |
+-------+-------+-------+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
|     1 | Apple |     0 |     14 |     63 |     17 |     52 |     77 |     39 |     19 |     77 |     14 |      95 |      24 |      72 |      44 |      90 |      65 |      58 |      61 |      88 |      34 |       3 |      79 |
|     2 | Apple |     0 |     66 |     22 |     23 |     79 |     86 |     16 |     32 |     40 |     67 |      64 |      14 |      13 |      39 |      67 |      64 |      47 |       3 |      20 |      91 |      40 |      21 |
|     3 | Apple |     0 |     77 |     56 |     13 |     73 |     70 |      7 |     92 |     85 |     45 |      29 |      53 |      69 |      70 |      42 |      79 |      85 |      89 |      93 |      44 |      73 |      61 |
|     4 | Apple |     0 |     90 |     49 |     45 |      1 |     69 |     54 |     97 |     47 |     78 |      62 |      88 |      54 |      96 |      59 |      37 |      76 |      14 |      25 |      45 |      17 |      39 |
|     5 | Apple |     0 |     22 |     58 |      2 |     92 |     49 |     85 |     14 |     58 |     13 |      82 |      67 |      63 |      59 |      37 |      16 |      45 |      68 |       9 |      15 |      35 |      53 |
|     6 | Apple |     0 |     73 |     91 |     11 |     34 |      5 |     47 |     23 |     72 |     46 |      28 |      91 |      47 |      36 |      53 |      11 |      74 |      38 |       1 |      59 |      62 |      37 |
|     7 | Apple |     0 |     99 |     45 |     26 |      7 |     61 |     13 |     44 |     76 |     86 |      68 |      63 |      71 |      40 |      56 |      54 |      37 |      46 |      60 |      49 |      33 |      39 |
|     8 | Apple |     0 |     79 |     24 |     58 |     98 |      2 |     50 |     34 |     53 |     67 |      15 |      56 |      98 |      96 |      19 |      83 |      36 |      80 |      55 |      73 |      55 |      69 |
|     9 | Apple |     0 |     18 |     20 |      8 |      2 |      4 |     46 |     80 |     64 |     63 |       7 |       4 |      27 |      61 |      94 |      68 |      16 |      33 |      89 |      52 |      72 |       7 |
|    10 | Apple |     0 |     81 |     39 |     75 |     83 |     56 |     60 |     27 |     23 |     94 |      33 |      10 |      69 |      61 |      75 |      67 |      93 |      47 |       4 |      72 |      47 |      87 |
|    11 | Apple |     0 |     19 |     56 |     72 |     67 |     63 |     11 |     43 |     42 |     61 |      14 |      57 |      91 |      19 |      26 |       2 |      95 |      39 |       3 |      90 |      87 |      42 |
|    12 | Apple |     0 |     87 |     93 |     52 |     95 |     58 |     68 |     94 |     97 |     24 |      54 |      18 |      34 |       9 |      31 |      11 |      30 |      61 |      94 |      23 |      32 |      98 |
|    13 | Apple |     0 |     32 |     41 |     37 |     26 |     49 |     19 |     53 |     61 |     77 |      47 |      81 |      60 |      12 |      11 |      72 |      89 |      83 |      27 |      19 |      13 |      46 |
|    14 | Apple |     0 |     40 |     20 |     93 |     95 |     69 |     55 |     90 |     85 |     42 |      81 |      97 |      11 |      74 |      28 |      19 |      15 |      99 |      43 |      23 |      67 |      62 |
|    15 | Apple |     0 |     60 |      4 |      9 |     57 |     59 |     81 |      4 |     27 |     76 |      27 |      47 |      25 |      42 |       3 |      53 |      23 |      14 |      28 |      91 |      24 |      67 |
|    16 | Apple |     0 |     29 |     57 |     35 |     13 |     34 |     18 |     76 |     53 |     54 |      16 |      27 |      58 |     100 |      27 |      69 |      48 |       4 |      37 |      98 |      41 |      61 |
|    17 | Apple |     0 |     58 |     30 |    100 |     51 |     15 |     23 |     11 |     45 |     31 |      91 |      53 |      74 |      38 |      93 |      23 |      66 |      71 |      93 |      74 |      94 |       9 |
|    18 | Apple |     0 |     72 |     84 |    100 |     56 |      2 |     77 |     70 |     68 |      5 |      14 |      60 |      38 |      28 |      38 |      37 |      79 |      54 |      46 |      83 |     100 |       8 |
|    19 | Apple |     0 |      3 |     59 |     33 |     45 |     33 |     83 |     28 |     96 |     35 |      81 |      43 |     100 |      42 |      12 |      42 |      23 |      55 |      35 |      40 |      66 |       3 |
|    20 | Apple |     0 |     41 |     64 |     38 |     95 |      1 |     63 |     83 |     56 |     63 |      17 |      85 |      54 |      53 |      59 |      11 |      70 |      65 |      12 |      13 |      80 |      82 |
|     1 | Apple |     1 |      8 |     57 |     53 |      9 |     85 |     36 |     45 |     58 |     44 |       4 |      20 |      99 |      42 |      25 |      57 |      96 |      64 |      74 |      80 |       6 |      74 |
|     2 | Apple |     1 |     67 |     79 |     87 |     87 |     48 |      6 |     15 |      7 |     82 |      27 |      22 |      95 |      45 |      42 |      61 |      92 |      74 |      10 |      82 |       5 |      21 |
|     3 | Apple |     1 |     44 |     62 |     14 |     86 |     36 |     10 |     37 |      2 |     78 |       3 |      53 |      16 |      36 |      10 |      25 |      42 |      80 |      92 |      18 |      25 |      35 |
|     4 | Apple |     1 |     83 |     38 |     60 |      6 |     61 |     14 |      6 |     51 |     67 |       8 |      13 |      36 |      18 |       1 |      92 |      15 |      55 |      24 |      29 |      23 |      88 |
|     5 | Apple |     1 |     31 |     90 |     33 |     79 |     75 |     39 |     80 |     36 |    100 |      98 |      74 |      50 |      16 |      14 |      88 |      53 |      35 |      75 |      49 |      95 |      17 |
|     6 | Apple |     1 |     51 |     34 |     61 |     23 |     95 |     69 |     58 |     62 |     82 |      63 |      20 |      99 |      63 |      18 |      48 |       9 |      90 |      50 |      85 |      59 |      87 |
|     7 | Apple |     1 |     83 |     55 |     72 |     16 |      5 |     36 |     54 |     20 |     84 |      99 |      43 |      80 |      27 |      16 |     100 |      42 |      49 |      17 |       8 |      69 |      86 |
|     8 | Apple |     1 |     59 |     20 |     56 |     89 |     15 |     81 |     42 |      2 |     80 |      43 |      21 |      98 |     100 |      80 |      10 |      70 |      87 |      61 |      17 |      16 |      43 |
|     9 | Apple |     1 |     14 |     66 |     92 |     49 |      7 |     86 |     28 |     27 |     30 |      83 |      10 |       5 |      76 |       1 |      52 |      41 |      27 |      44 |      86 |      17 |      40 |
|    10 | Apple |     1 |     60 |     64 |     57 |     71 |     72 |     10 |     75 |     34 |     70 |      15 |      49 |      96 |      83 |      37 |      95 |      84 |      65 |      30 |       1 |      32 |      70 |
|    11 | Apple |     1 |     92 |     66 |     41 |     69 |     86 |     95 |      7 |     31 |     72 |      68 |      64 |      37 |      46 |      98 |      45 |      35 |      14 |      23 |      37 |      32 |      99 |
|    12 | Apple |     1 |     80 |     49 |     48 |     68 |     64 |     84 |     44 |     92 |      4 |      37 |      32 |      20 |      15 |      95 |      67 |      84 |      61 |      16 |      24 |      80 |       6 |
|    13 | Apple |     1 |     57 |     19 |     77 |     91 |     36 |     56 |     20 |     15 |     64 |       1 |      12 |      86 |      40 |      42 |      68 |      10 |      32 |      96 |      24 |      35 |      73 |
|    14 | Apple |     1 |     24 |      5 |     34 |     60 |     68 |      8 |     72 |     71 |     38 |      21 |      11 |      51 |      51 |      27 |      44 |      22 |      71 |      24 |      98 |      16 |      40 |
|    15 | Apple |     1 |     40 |     64 |     59 |     48 |     80 |     43 |     16 |     84 |     65 |      13 |      83 |      85 |      45 |      17 |      97 |      40 |      62 |      72 |      31 |      34 |      25 |
|    16 | Apple |     1 |     24 |    100 |     32 |     93 |     28 |     15 |     19 |     80 |     63 |      85 |      38 |       7 |      63 |      26 |      69 |      90 |      30 |      26 |      98 |      88 |       4 |
|    17 | Apple |     1 |     41 |     28 |     27 |     72 |     57 |     80 |     26 |     72 |     91 |      27 |      69 |      36 |       3 |      24 |      20 |       5 |      66 |      96 |      72 |      36 |      45 |
|    18 | Apple |     1 |     41 |     21 |     78 |     72 |     23 |     46 |     23 |     51 |     15 |      50 |      35 |      41 |      92 |      25 |      77 |      59 |      63 |      75 |      53 |      49 |      51 |
|    19 | Apple |     1 |     72 |     99 |     65 |     82 |      1 |     62 |     73 |      4 |      8 |      67 |      63 |      10 |      98 |      70 |      95 |      28 |      60 |      80 |      41 |      20 |      50 |
|    20 | Apple |     1 |     33 |      2 |     68 |     77 |     94 |     58 |     83 |     43 |     79 |       4 |      39 |      10 |      81 |      42 |      86 |      53 |      21 |      85 |      99 |      64 |      52 |
|     1 | Apple |     2 |     78 |     91 |     63 |     29 |     49 |     50 |     27 |     75 |     83 |      24 |      38 |      19 |      60 |      74 |      86 |      64 |       9 |      79 |      25 |      63 |      47 |
|     2 | Apple |     2 |     98 |     33 |     44 |      3 |     36 |     86 |     73 |     38 |     15 |      23 |      34 |      30 |      65 |      15 |      17 |      84 |      24 |      22 |      64 |      83 |      97 |
|     3 | Apple |     2 |     28 |      7 |     81 |     21 |     96 |     90 |     47 |      3 |     62 |      70 |      91 |      82 |      14 |      16 |      94 |       9 |      36 |      47 |      71 |       8 |      27 |
|     4 | Apple |     2 |     96 |     17 |     21 |     71 |     20 |      1 |     63 |     69 |     10 |      62 |      52 |      22 |      97 |      41 |      92 |      94 |      14 |      81 |      78 |       7 |       8 |
|     5 | Apple |     2 |     91 |     57 |     53 |     69 |     38 |     46 |     49 |     80 |     73 |       9 |     100 |      25 |      26 |      21 |      89 |      30 |      44 |      21 |      15 |      10 |      24 |
|     6 | Apple |     2 |     46 |     48 |     66 |     19 |     83 |     69 |     48 |     76 |     39 |      57 |      93 |      97 |      11 |      48 |      76 |      77 |      86 |      41 |      46 |      58 |     100 |
|     7 | Apple |     2 |     37 |     84 |     39 |      3 |     54 |     81 |     13 |     81 |     60 |      73 |       7 |      21 |      28 |      19 |      45 |       3 |      56 |      93 |      66 |      96 |      85 |
|     8 | Apple |     2 |     71 |     72 |     95 |     65 |     15 |     62 |     47 |     54 |      8 |      75 |       3 |      39 |      62 |      62 |      33 |      43 |       5 |      30 |      69 |      73 |      78 |
|     9 | Apple |     2 |     80 |     41 |     35 |     83 |     10 |     69 |     92 |      9 |     18 |      51 |      75 |       6 |      48 |      59 |      41 |      58 |      40 |      31 |      37 |      58 |      50 |
|    10 | Apple |     2 |     46 |     65 |     46 |     65 |     47 |     89 |     62 |     25 |     69 |       3 |      30 |      45 |      28 |      42 |      76 |      76 |      33 |      32 |      81 |      98 |      23 |
|    11 | Apple |     2 |     29 |     24 |     15 |     44 |     80 |     98 |     56 |     81 |     69 |       7 |      87 |       5 |      23 |      11 |      35 |      45 |      90 |      96 |       5 |      41 |      33 |
|    12 | Apple |     2 |     17 |     90 |     25 |     17 |     82 |      1 |     43 |    100 |     39 |      68 |       1 |      66 |      68 |      20 |      12 |      12 |      91 |      28 |      20 |      78 |      89 |
|    13 | Apple |     2 |     79 |     89 |     58 |      5 |     90 |     48 |     38 |     68 |     72 |      77 |      17 |      67 |       8 |      54 |      61 |      57 |      34 |      72 |      74 |       9 |      17 |
|    14 | Apple |     2 |      7 |     55 |     67 |     87 |     98 |     20 |     34 |     71 |     39 |      62 |      30 |       8 |      62 |      24 |      62 |      95 |      28 |      60 |      17 |      61 |      35 |
|    15 | Apple |     2 |     78 |     39 |     43 |     18 |     53 |     92 |     42 |     43 |     53 |      21 |      57 |     100 |      50 |      64 |       7 |      82 |      68 |      34 |      54 |      68 |      64 |
|    16 | Apple |     2 |     33 |      6 |     22 |      3 |     36 |     16 |     80 |     49 |     22 |      13 |      17 |      37 |      34 |      82 |      54 |      85 |      12 |      97 |     100 |      20 |      15 |
|    17 | Apple |     2 |     91 |     27 |     79 |     70 |     32 |      8 |     25 |     26 |     61 |      13 |      63 |      21 |      47 |      85 |      66 |      29 |       7 |      24 |      23 |      62 |      47 |
|    18 | Apple |     2 |     76 |     66 |     93 |     91 |     49 |     77 |     92 |     27 |     57 |      15 |      53 |       3 |      70 |      24 |      39 |      16 |      83 |       8 |      82 |      23 |      85 |
|    19 | Apple |     2 |     34 |     25 |     17 |     52 |     81 |     68 |     87 |     69 |     67 |      23 |      23 |      32 |      47 |      47 |      59 |       3 |      63 |      92 |      76 |      59 |      54 |
|    20 | Apple |     2 |     74 |     63 |     74 |     87 |      2 |     59 |      7 |     11 |     13 |      17 |      87 |      78 |       7 |      11 |      31 |      71 |      16 |      92 |      67 |      84 |      95 |
+-------+-------+-------+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+

Can you explain what "Excel calculations are used to generate the output for Price" means? Then, where the result of each "calculation" is returned? In column B:B, on the next empty row? — FaneDuru, Mar 23 '20 at 15:47
The excel calculations are within the Calcs worksheet and will generate an output in the cell named "Price" in the Calcs worksheet. I primarily need help in solving the loop function syntax `For r = 1 To NoRows Step 20 .Range("V1").Value = Application.Index(vInput1, r, 0)` — sjedi, Mar 23 '20 at 15:54
I was asking that with a reason... Using an array may tremendously increase the code speed. But, if your concern is how to loop using step, would be enough that inside of the existing loop to insert another one `For i = r + 0 to 19` `'do what you need` `Next i`. Besides all that, you use `Worksheets("Input")` to calculate rows number and then `Worksheets("Calcs")` to iterate based on the previous calculated number... — FaneDuru, Mar 23 '20 at 16:01
So at the moment you are transferring one row to V1 and you want to transfer 20? — SJR, Mar 23 '20 at 16:03
@SJR, yes I am transferring one row to V1, but now I am looking to transfer 20 rows.@FaneDuru, I am looking to fetch 20 rows of data from `Worksheets("Input")` to `.Range("V1")` and then fetch the next 20 rows of data from `Worksheets("Input")` and so forth — sjedi, Mar 24 '20 at 00:37
I understand that, but you missed my point, I'm afraid... If you would show us what "calculations are done after you copy something to "V1", we maybe would supply a much faster solution, using arrays and ALSO CALCULATE. The result would be returned at once, at the end of processing. but we must know more: what "TINPUT" is and other names (probably) you use. — FaneDuru, Mar 24 '20 at 09:46
@FaneDuru it is actually a cash flow model with irregular cash inflow and outflow schedules and IRR calculations, which makes it difficult to operationalise in the VBA environment. Hence, I am only looking for a loop function to cycle through the various batches of trials — sjedi, Mar 24 '20 at 15:28
So, solution provided by SJR does not do the job as you need? Don't you need to paste in "B2" the range of 20 rows and 21 columns? — FaneDuru, Mar 24 '20 at 16:23
I have found the solution on another forum by defining N as a 'counting' cell and indexing `.Range("V1")` to the `.Range("TINPUT")` using a Excel formula 20*N-19, 20*N-18... and so forth together with some helper columns `For r = 1 To NoRows/20 .Range("N").Value = r vPrice = .Range("Price").Value vCoPrice(r, 1) = vPrice Next r` — sjedi, Mar 25 '20 at 00:38
@FaneDuru, are you expecting SJR's solution to be marked as correct? given your question — sjedi, Mar 25 '20 at 00:43

score 3 · Answer 1 · answered Mar 24 '20 at 09:51

3

Perhaps you can adapt this example. There are thirty rows of data and the loop will transfer rows 1-10 to H1, then rows 11-20 to H1 and then 21-30 to H1.

Sub x()

Dim v, n As Long, i As Long

n = 10 'number of rows of data we want

v = Range("A1").CurrentRegion.Value

For i = 1 To 3
    Range("H1").Resize(n, 4).Value = Application.Index(v, Evaluate("ROW(" & (i - 1) * n + 1 & ":" & i * n & ")"), Array(1, 2, 3, 4)) 'Array(1, 2, 3, 4) is number of columns of data
Next i

End Sub

answered Mar 24 '20 at 09:51

SJR

22,986
6
18
26

Very nice way of slicing the array! Did you find a solution to directly input it in another array? Without loading it from the returned range... – FaneDuru Mar 24 '20 at 11:00
1

@FaneDuru - thanks. Yes, the 20 rows could be assigned to another array instead of a range (even an unsized array, eg. `dim v2 // v2=application.index(etc)` – SJR Mar 24 '20 at 11:22
1

FYI - you might be interested in a systematic overview of rather unknown features of the `Application.Index()` function at [Some pecularities ...](https://stackoverflow.com/questions/51688593/excel-vba-insert-new-first-column-in-datafield-array-without-loops-or-api-call/51714153#51714153) – T.M. Mar 24 '20 at 11:36
1

@T.M. - thanks. Are you aware of a way round having to use `Array(1, 2, 3, 4)` if you want all columns? If you have 20 columns it's a bit of a bore and I couldn't find a workaround. – SJR Mar 24 '20 at 11:44
1

I used VBA for more the 17 years. But in the last 10 I didn't use it at all, except from some Corel VBA projects. Three months ago I "discovered" the meaning of 'reputation' on stackoverflow... I had an account for years when I asked three questions, but I answered them by myself. Now I take the community like an opportunity to test my rusty skills and also learn. **This way of slicing was a revelation for me**! – FaneDuru Mar 24 '20 at 11:46
1

A way round having to use Array(1, 2, 3, 4)? - yes, with variant array: `ColNums = Application.Transpose(Evaluate("row(1:4)"))` and eventually `ReDim Preserve ColNums(0 To UBound(ColNums) - 1)` to make it flat and zero-based (I'd use a function:) – T.M. Mar 24 '20 at 12:06
1

@SJR: I found a solution to replace `Array(1, 2, 3, 4)`: `Dim arr()` then, for the above code: `Redim arr(1 To Ubound(v, 2))`. And it is able to replace the manual created array in the next way: `For i = 1 To Ubound(v, 2)` `arr(i) = i` `Next i`. And then, using it like: `Range("H1").Resize(n, 4).Value = Application.Index(v, Evaluate("ROW(" & (i - 1) * n + 1 & ":" & i * n & ")"), arr)`. Possible to be a way of more elegantly building the array, I just wanted to check if the idea works... – FaneDuru Mar 24 '20 at 12:16
1

@FaneDuru - another link https://www.excelforum.com/tips-and-tutorials/758402-vba-working-with-areas-within-2d-arrays.html – SJR Mar 24 '20 at 12:26

score 0 · Accepted Answer · answered Mar 28 '20 at 07:58

0

Set counter cell "N"

For r = 1 To NoRows/20 .Range("N").Value = r vPrice = .Range("Price").Value vCoPrice(r, 1) = vPrice Next r

answered Mar 28 '20 at 07:58

sjedi

43
1
7

Excel VBA loop through 10,000 sets of rows, each set containing 20 rows

2 Answers2

Linked