Excel VBA遍历10,000组行，每组包含20行 [英] Excel VBA loop through 10,000 sets of rows, each set containing 20 rows
本文介绍了Excel VBA遍历10,000组行，每组包含20行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
如何将当前按行循环的Excel VBA代码转换为20行的循环?
How can I convert my Excel VBA code, which currently loops by row, to loop through sets of 20 rows?
我知道Step函数可能与以下行.Range("V1").Value = Application.Index(vInput1, r, 0)一起使用.但是，我不确定如何修改代码以遍历每批20行并向下移20行以遍历下一组20行.
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.
注意:在将每20行的行粘贴"到.range(V1)之后，将使用Excel计算生成Price的输出.我打算为每组20行生成Price，我总共有10,000套(或试用版)
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
我的试验1和2的样本如下所示.每个试验由20行(1至20级)和21列(1至21年级)组成.总共我要进行10,000次试验
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 |
+-------+-------+-------+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
推荐答案
设置计数器单元格"N"
Set counter cell "N"
For r = 1 To NoRows/20 .Range("N").Value = r vPrice = .Range("Price").Value vCoPrice(r, 1) = vPrice Next r
这篇关于Excel VBA遍历10,000组行，每组包含20行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
