Stepping through array elements

[Lenna]

Hello,

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 (Col B). The range is displayed in a userform Listbox.

How do I 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. I know how to cycle through array elements using a for each loop but I can't figure out how to increment only by one element in either direction each time a button is pressed.

Here is what I've got so far for selecting ranges:

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("A" & c.Row).Resize(, 2)
                        Debug.Print rng
                    Else
                        Set rng = Union(rng, .Range("A" & 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

Thank you very much!

 

[oldbrewer]

Cell E3 is cells(3,5) so if you set j=3 and k=5 it becomes cells(j,k)

I would have thought if you had 4 macros "triggered" by 4 buttons labelled up, down, left, right, each macro would change the value of either j or k by plus 1 or minus 1

 

[Lenna]

Cell E3 is cells(3,5) so if you set j=3 and k=5 it becomes cells(j,k)

I would have thought if you had 4 macros "triggered" by 4 buttons labelled up, down, left, right, each macro would change the value of either j or k by plus 1 or minus 1

So, i need to get rid of for loop (Lbound to Ubound) and output the array to Sheet instead? Offset by one each time the button is clicked ( up or down) and feed the selected value into code that selects each range? Is this what you are thinking? Please clarify. Thanks

 

[oldbrewer]

once the array is printed and displayed I suppose the user could select one of 4 cells that result in the appropriate macro being called and run, several times, until say finish is selected....I was just trying to throw a possible way forward not the detail...advise you bump the thread and hope a macro expert sees it

 

[Lenna]

How do I bump this thread? Am I in the wrong forum?

 

[My Aswer Is This]

I like helping people with Vba scripts using UserForms. But when they post a Macro they already have which in most cases they copied from some other forum and want it modified to fit their needs then most times I bow out and let someone else tackle the question.
I like knowing what your ultimate goal is and then I will write you a script that will do that. But we do need to have all the details.

 

[MickG]

This could be the basis for your Range selection from Two CommandButtons
Clicking Either button will result in the Results of the array incrementing Either up or down as per Button selection.

Option Explicit
Dim Num As Long, Myray As Variant
Private Sub CommandButton1_Click()
If Num < 20 Then
Num = Num + 1
CommandButton1.Caption = Myray(Num)
Range("B1") = Myray(Num)
End If
End Sub


Private Sub CommandButton2_Click()
If Num > 1 Then
Num = Num - 1
CommandButton2.Caption = Myray(Num)
Range("B1") = Myray(Num)
End If
End Sub


Private Sub CommandButton3_Click()
'Imagine this is your code that creates the Array then !!!!
Myray = Application.Transpose(Range("A1:A20").Value)
End Sub

 

[Lenna]

I like helping people with Vba scripts using UserForms. But when they post a Macro they already have which in most cases they copied from some other forum and want it modified to fit their needs then most times I bow out and let someone else tackle the question.
I like knowing what your ultimate goal is and then I will write you a script that will do that. But we do need to have all the details.

The ultimate goal is display each range in a listbox. 'Left' and 'Right' buttons will be used to move from range to range. I've added SpinButton (up and down) for user to sort items in the listbox for each range. Once each range is sorted to their liking I have code taht will overwrite the existing selected range with sorted range in the listbox. I can post the rest of the code if it would be helpful/beneficial to anyone?

I found code on-line for SpinButton and also code that would overwrite the existing range with sorted items in the listbox. I did make some changes to the code and grateful that I'm able to make the changes after learning VBA on my own. Without on-line help, I would be hitting my head on my desk trying to write code from scratch (like your avatar)

 

[Lenna]

This could be the basis for your Range selection from Two CommandButtons
Clicking Either button will result in the Results of the array incrementing Either up or down as per Button selection.

Option Explicit
Dim Num As Long, Myray As Variant
Private Sub CommandButton1_Click()
If Num < 20 Then
Num = Num + 1
CommandButton1.Caption = Myray(Num)
Range("B1") = Myray(Num)
End If
End Sub


Private Sub CommandButton2_Click()
If Num > 1 Then
Num = Num - 1
CommandButton2.Caption = Myray(Num)
Range("B1") = Myray(Num)
End If
End Sub


Private Sub CommandButton3_Click()
'Imagine this is your code that creates the Array then !!!!
Myray = Application.Transpose(Range("A1:A20").Value)
End Sub

20 and 1 would be Lbound and Ubound of the array, correct?
And then I need:

If UCase(c.Text) = [B]num[/B] Then
If rng Is Nothing Then
                        Set rng = .Range("B" & c.Row).Resize(, 1)
                        Debug.Print rng
                    Else
                        Set rng = Union(rng, .Range("B" & c.Row).Resize(, 1))
                        Exit For
                        Debug.Print rng
                    End If

Would you put all of the code into a Form or no? I don't think I need to transpose the array with this solution?

Thanks!

 

[oldbrewer]

reply with single word bump