Moving through sequential array items

Question

The code below creates an array of unique values from values in Column A. Each selected array element is used to select a range on the sheet. The range is displayed in a userform Listbox.

I would like help with code that would allow the user to scroll through each array ‘MyarUniqVal’ element via two form buttons Right ‘>>’ and Left ‘<<’. Each time a button is pressed a sequential array item will be selected and a new range will populate the Listbox.

Any help would be greatly appreciated.

Thanks,

Please see the code below:

    Sub testRange3()

    Dim lastrow, i, j As Long
    Dim c As Range, rng As Range
    Dim MyArUniqVal() As Variant


ReDim MyArUniqVal(0)
'With ActiveSheet
 With ThisWorkbook.Worksheets("Temp")

    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

    For i = 1 To lastrow
        If .Cells(i, 1).Value <> .Cells(i + 1, 1).Value Then
            MyArUniqVal(UBound(MyArUniqVal)) = .Cells(i, 1).Value
            ReDim Preserve MyArUniqVal(UBound(MyArUniqVal) + 1)
        End If
    Next
    ReDim Preserve MyArUniqVal(UBound(MyArUniqVal) - 1)
End With

For j = LBound(MyArUniqVal) To UBound(MyArUniqVal)
'Prints out each array to Immediate Window
    Debug.Print j
'Prints out unique values from Column A stored in array to Immediate Window
    Debug.Print MyArUniqVal(j)
Next


With ThisWorkbook.Worksheets("Temp")
'changed to ActiveSheet
    'With ActiveSheet
        For Each c In .Range("A1:A" & lastrow)
For j = LBound(MyArUniqVal) To UBound(MyArUniqVal)
    If UCase(c.Text) = j Then
                'If UCase(c.Text) = "B" Then
                    If rng Is Nothing Then
                        Set rng = .Range("B" & c.Row).Resize(, 2)
                        Debug.Print rng
                    Else
                        Set rng = Union(rng, .Range("B" & c.Row).Resize(, 2))
                        Exit For
                        Debug.Print rng
                    End If
                End If
           Next
        Next c
    End With

    If Not rng Is Nothing Then rng.Select

End Sub

Answer 1

See the following code to get you heading the the right direction. I took the approach of adding another listbox that displayed the available prefixes to help the user see what was available and then searching the data column for entries containing the selected prefix.

Hopefully you will be able to adapt the name of the variables and objects to whatever you are currently using. Let me know if anything needs clarification. Best of luck with your project.

My sample form code:

Private Sub cmdBack_Click()

    code_frmMain.IncrementValue (0)
    
End Sub

Private Sub cmdNext_Click()
    
    code_frmMain.IncrementValue (1)
    
End Sub

Private Sub lstPrefixes_Change()

    code_frmMain.DisplayNext

End Sub   

Private Sub UserForm_Initialize()

    code_frmMain.testRange3

End Sub

My sample program code:

'  This subroutine will search column B for the selected value
Sub DisplayNext()

    Dim searchTerm As String
    Dim lastRow As Long
    Dim i As Integer
    
    ' clear frmMain.lstResults
    frmMain.lstResults.Clear
        
    For i = 0 To frmMain.lstPrefixes.ListCount - 1
    
         If frmMain.lstPrefixes.Selected(i) = True Then
         
            searchTerm = frmMain.lstPrefixes.List(i)
            Exit For   ' exits once selected item is found
            
         End If
    
    Next i
    
    'Debug.Print searchTerm

    With Sheets("Temp")
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    End With
    
    
    For i = 1 To lastRow
    
        If InStr(Cells(i, 2).Value, searchTerm) Then
        
            frmMain.lstResults.AddItem (Cells(i, 2).Value)
                
        End If
            
    Next i
 
End Sub

' increments value. input direction: 0 is down and 1 is up
Sub IncrementValue(direction As Integer)
    
    Dim currentIndex As Integer
    currentIndex = -1
      
    For i = 0 To frmMain.lstPrefixes.ListCount - 1
    
         If frmMain.lstPrefixes.Selected(i) = True Then
         
            currentIndex = frmMain.lstPrefixes.ListIndex
            Exit For   ' exits once selected item is found
            
         End If
    
    Next i
    
    ' defaults to first item if none selected
    If currentIndex = -1 Then
        frmMain.lstPrefixes.Selected(0) = True
        currentIndex = 0
    End If
    
    
    If direction = 0 Then
        
        ' prevents listIndex from being invalid
        If currentIndex = 0 Then
        
            frmMain.lstPrefixes.Selected(frmMain.lstPrefixes.ListCount - 1) = True
        
        Else
        
            frmMain.lstPrefixes.Selected(currentIndex - 1) = True
        
        End If
    
    Else
    
        If currentIndex = frmMain.lstPrefixes.ListCount - 1 Then
        
            frmMain.lstPrefixes.Selected(0) = True
        
        Else
    
            frmMain.lstPrefixes.Selected(currentIndex + 1) = True
            
        End If
            
    End If

End Sub

Note that I also added this to the bottom of your testRange3() to use that data that you had already gathered:

For i = 0 To UBound(MyArUniqVal)

    frmMain.lstPrefixes.AddItem (MyArUniqVal(i))
    
Next i

Sample Data:

Running on user form: