如何在Excel VBA中为矩阵单元格使用嵌套循环 [英] How to use nested loop for a Matrix cell in excel vba

问题描述

当前的Excel工作表结构

filteredStartRow = A2 detailsS​​tartRow = D2

filteredStartRow = A2 detailsStartRow = D2

※i = x(获取filteredItemCount的值，例如:2、3、2)
※j = y(获取detailsItemCount的值，例如:4、5、3)

※i = x（get value for filteredItemCount, e.g: 2, 3, 2）
※j = y（get value for detailsItemCount, e.g: 4, 5, 3）

我被困住了，我可以多高效地移动到第二个filterStartRow(A4)和detailsS​​tartRow(D10).并继续直到最后一个过滤器和详细信息StartRow.

I am stuck, how efficiently I can move to second filteredStartRow (A4) and detailsStartRow (D10). and continue until the last filter and details StartRow.

我在做什么: 获取ItemCount(例如:x，y)以在过滤器和详细信息项目中运行嵌套循环. 由于我已经知道过滤器和详细信息的下一项计数，因此我只需要更改我的NEXT filterStartRow和detailsS​​tartRow. 如何使用(i，j)的任何动态设置以及设置STARTROW来构建我的Loop? 谁能帮忙提供代码.

What I am doing: Get ItemCount (e.g: x, y) to run the nested loop within filter and details item. Since I already know the next item count for filter and details, so I just need to change my NEXT filterStartRow and detailsStartRow. How can construct my Loop using any dynamic settings for (i, j) as well as setting up STARTROW? Can anyone please help with code.

这是我的代码，仅适用于第一个循环(绿色边框).

Here is my code, which only works for the very first loop (green bordered).

startRow = 2
startRow1 = 2
nextDetailsRow = 0

For i = 1 To noOfFilteredItem (e.g:3)

mapFilteredItemCount = Worksheets("Sheet1").Cells(startRow, 3).Value
detailsItemCount = Worksheets("Sheet1").Cells(startRow1, 6).Value

With ThisWorkbook.Worksheets("Sheet1")
For m = 1 To mapFilteredItemCount 
For n = 1 To detailsItemCount 
If .Cells((startRow + m) - 1, 2) = .Cells((startRow1 + n) - 1, 5) Then
If IsEmpty(.Cells((startRow1 + n) - 1, 8).Value) = True Then
.Cells((startRow1 + n) - 1, 8).Value = "Deliver"
nextDetailsRow = nextDetailsRow + startRow + n
Else
GoTo NextIteration
End If
End If
NextIteration:

Next n
Next m
End With
Next i

我的问题是:
-循环(m，n)完成后，我需要动态更新STARTROW和STARTROW1.
-如何分配STARTROW和STARTROW1变量以接收下一个起始行值.

My Problem is:
- I need to update the STARTROW and STARTROW1 dynamically once a loop (m, n) is completed.
- How can I assign STARTROW and STARTROW1 variable to receive next starting row value.

我的逻辑:
-我当时正在考虑将所有STARTROW和STARTROW1数字保留在一个数组中，然后从数组中获取值.
例如:
filteredItemRowArray()= 2，4，7(※已过滤项目的开始行号)
detailsItemRowArray()= 2、6、11(※开始显示详细信息的行号)

My Logic:
- I was thinking to keep all the STARTROW and STARTROW1 number into an array, then get value from the array.
for example:
filteredItemRowArray() = 2, 4, 7 (※starting row numbers for filtered item)
detailsItemRowArray() = 2, 6, 11 (※starting row numbers for details item)

但是我不能安排这个数组来保留行号值.

but I could not arrange this array to keep row number values.

任何人都可以帮助我，我真的很感谢您的编程智能. 如果您有任何疑问或理解上的问题，请告诉我. 非常感谢.

Could anyone please help me, I truly appreciate your programming SMARTNESS. If you have any questions, or understanding problem, please let me know. Thank you very much.

推荐答案

我会尝试相同的想法.用数字循环遍历数组.请尝试以下代码:

I would try the same idea. Looping through an array with the numbers. Please try the following code:

Sub Outer_Loop()
Dim StartrowArr, Startrow1Arr, I As Integer
Dim Startrow As Long, Startrow1 As Long
StartrowArr = Array(2, 4, 7)
Startrow1Arr = Array(2, 6, 11)
For I = LBound(StartrowArr) To UBound(StartrowArr)
    Startrow = StartrowArr(I)
    Startrow1 = Startrow1Arr(I)
    Debug.Print "Loop " & I, "Startrow: " & Startrow, "Startrow1: " & Startrow1
    ' your code
Next I
End Sub

这篇关于如何在Excel VBA中为矩阵单元格使用嵌套循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！