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

I don't know how to program the buttons to scroll through each elements of the described array. There is nothing wrong with the code that I’ve posted. I need additional code. Thanks — user3781528
– user3781528, Commented Dec 18, 2015 at 20:34
You can set your array and a counter as public variables. Then when the button is pushed the counter moves and calls a sub that finds that value in the public array and post the outcome like you have. — Scott Craner
– Scott Craner, Commented Dec 18, 2015 at 21:12
SO is not a code for me service. Do some research and try on your own with the hints above. The code you have does not show an attempt on your part to do what you are asking. — Scott Craner
– Scott Craner, Commented Dec 18, 2015 at 21:50

Community · Accepted Answer · 2020-06-20 09:12:55Z

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:

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 19, 2015 at 17:50

NinjaLlama

1653 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3781528 Over a year ago

The only thing, it's not selecting each rng on sheet as it used to. If Not rng Is Nothing Then rng.Select

NinjaLlama Over a year ago

I set a break point in your code where If rng Is Nothing Then Set rng = Union(rng, .Range("B" & c.Row).Resize(, 2)) and it was never encountered. I would guess that the If statement If UCase(c.Text) = j Then never returns true

user3781528 Over a year ago

ok, thanks.. I move rng code to DisplayNext().. it works fine now

Collectives™ on Stack Overflow

Moving through sequential array items

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related