Search code examples
excelvbafor-loopvariablesnested-loops

Excel VBA For Nested Loop with IF and variables

I have 10 vacuum sense lines that can be used (VacLine(x) variable), those are odd numbers (from 1 to 19) located on a Multiple Selection ListBox. Depending on the selection of these options, I would like to assign a single value to each of the 10 VacLine(x) variables.

enter image description here

The driving number in this case would be selected from the ListBox, once that number is found, it should assign it to the VacLine(x) and then go to the Next x from the last selected value it counted. The minimum selection is 1, and the max is 10, and not in a specific order.

So for example shown, I selected 3, 17 & 19 then VacLine1 = 3, VacLine2 = 17 & VacLine3 = 19, VacLine4 to VacLine10 = 0. VacLinesList is my ListBox. VacLine(x) are my variables Dimmed as Dim VacLine(1 To 10) As Integer

    With VacTC.VacuumLinesList
    
    For q = 1 To 10
        v = 0
        For v = v To .ListCount - 1
            If VacLine(q) = "" Then
                If .Selected(v) Then
                VacLine(q) = .List(v)
                Exit For
                End If
            Exit For
            End If
        Next v
    Next q
    
End With

I don't know how to force it to assign the value found to VacLine(x) and then go to the next x but starting at the last selected value found.

Solution

  • Assuming VacuumLinesList is your ListBox and VacLine is your array (already Dimmed)

    With VacuumLinesList
    Dim i As Long, j As Long
    j = LBound(VacLine)
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            VacLine(j) = .List(i)
            j = j + 1
        End If
    Next i
    ' produce test output
    For i = LBound(VacLine) To UBound(VacLine)
        Debug.Print i, VacLine(i)
    Next i
End With

    ... i is the ListBox item index and j is the array index