Excel VBA loop through 10,000 sets of rows, each s

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 |
+-------+-------+-------+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+

回答1:

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

回答2:

Set counter cell "N"

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