excel - Looping through two cells with specified ranges - Stack Overflow

admin管理员组

文章数量:1336195

I currently have a loop that iterates through the number of units from 1 to 20. However, I’d like to add a second loop to run concurrently. Specifically, for every multiple in a range (i.e., 10 to 20), I want to loop through the number of units. Could someone help me modify my code to include the multiple loop on top of the unit loop? Thanks for your help!

MAXUNIT = 20

MULTIPLE = the cell that can be set between 10-20 for the multiple

NUMBER = the cell that can be set between 1-20 for the number of units

Sub Calculate()

Application.ScreenUpdating = True

'Loop through multiples (10-20) & number of units (1-20)

Dim i As Integer
Dim A As Integer
Dim B As Integer
Dim Counter As Integer

Range("MULTIPLE").Value = 10

A = 1
B = Range("MAXUNIT").Value
Counter = A
i = A - 1

Do Until i = B

        Range("NUMBER").Value = Counter
        
        i = i + 1
        Counter = Counter + 1
    Loop

Range("NUMBER").Value = 1

End Sub

I currently have a loop that iterates through the number of units from 1 to 20. However, I’d like to add a second loop to run concurrently. Specifically, for every multiple in a range (i.e., 10 to 20), I want to loop through the number of units. Could someone help me modify my code to include the multiple loop on top of the unit loop? Thanks for your help!

MAXUNIT = 20

MULTIPLE = the cell that can be set between 10-20 for the multiple

NUMBER = the cell that can be set between 1-20 for the number of units

Sub Calculate()

Application.ScreenUpdating = True

'Loop through multiples (10-20) & number of units (1-20)

Dim i As Integer
Dim A As Integer
Dim B As Integer
Dim Counter As Integer

Range("MULTIPLE").Value = 10

A = 1
B = Range("MAXUNIT").Value
Counter = A
i = A - 1

Do Until i = B

        Range("NUMBER").Value = Counter
        
        i = i + 1
        Counter = Counter + 1
    Loop

Range("NUMBER").Value = 1

End Sub

• excel
• vba

Share Improve this question Follow asked Nov 19, 2024 at 21:00 DagobertDagobert 7377 bronze badges 3

• 1 You can use a nested loop: For unit = 1 to 20: For mult = 10 to 20:[do something with unit and mult]: Next mult: Next unit – Tim Williams Commented Nov 19, 2024 at 21:06
• Thanks, Tim! Would you be so kind as to write that out? I would be very grateful! – Dagobert Commented Nov 19, 2024 at 21:07
• That's all I could write since it's unclear what it is you're doing above. – Tim Williams Commented Nov 19, 2024 at 21:33

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

Like this, though it's unclear what this is for:

Sub Calculate()
    
    Dim unit As Long, mult As Long 'prefer Long over Integer
    
    Application.ScreenUpdating = True
    
    For unit = 1 To Range("MAXUNIT").Value
        
        Range("NUMBER").Value = unit
        
        For mult = 10 To 20
            Range("MULTIPLE").Value = mult
        Next mult
    
    Next unit
    
    Range("NUMBER").Value = 1

End Sub

Nesting Loops

Sub UnitsForMultiples()
    
    ' Define constants.
    Const LAST_MULTIPLE As Long = 20
    Const FIRST_UNIT As Long = 1

    ' Reference the worksheet.
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!

    ' Retrieve the first multiple.
    Dim FirstMultiple As Long: FirstMultiple = ws.Range("MULTIPLE").Value
    ' Retrieve the last unit.
    Dim LastUnit As Long: LastUnit = ws.Range("MAXUNIT").Value
    
    Dim Multiple As Long, Unit As Long
    
    ' Loop through the multiples...
    For Multiple = FirstMultiple To LAST_MULTIPLE
        ' ... and for each multiple, loop through the units...
        For Unit = FIRST_UNIT To LastUnit
            ' ... copying each unit to the Number cell...
            ws.Range("NUMBER").Value = Unit
            ' Do something 'more' useful, e.g.:
            Debug.Print Multiple, Unit
        Next Unit
    Next Multiple
    
    ' Reset the Number cell.
    ws.Range("NUMBER").Value = 1
    
End Sub

本文标签： excelLooping through two cells with specified rangesStack Overflow