Advice for simple code please

[ipbr21054]

Morning,

I have a worksheet called POSTAGE
First row with data is row 8 then down the page.
Column G are dates BUT there are a few Text entries.

These text enteries are what i am interested in & are as follows.
RETURNED, LOST, UNKNOWN, COLLECTION, RECEIVED, NO DATE

I will make a userform where i can click a command button which will then specifically search for the text entry names mentioned in column G
These Text entries will then be shown in a list box on the userform.

Clicking on a Text Entry will then go to the cell in question at column G

If it helps all entries other then thos mentioned will be dates or N/A

 

[mikerickson]

It sounds like you are duplicating the functionality of AutoFilter or Find.

 

[ipbr21054]

I would assume that those you mention would need to be typed in and searched one at a time.

I wish my code to just go search & put results In list box.
There’s only ever going to be say 20 or less but this is over at present 1300 rows.
As time goes in some will be changed to a date so results will be less.

To make things even easier / quicker start the search from current last row with data in the go up.
Then I would say the first 300 rows will return what I need. Starting from too then down first 1000 rows will return nothing. I don’t wish to be limited be having a specific range so last row up will be perfect.

thanks.

 

[mikerickson]

I was thinking of clicking the boxes in AutoFilter that will show only those rows with the entry you indicate.
But It sounds like you have a plan. What is your question?

 

[ipbr21054]

I’m looking for a code that will do what I need from user form.

 

[mikerickson]

What do you have?

 

[tico_ocit]

For the example, I created a userform with a combobox, when you select on of those, it selects the row on column G.
Adapt to what you want.

Private Sub cmbbox1_Click()

row_index = Me.cmbbox1.Column(1)

ActiveSheet.Range("G" & row_index).Select


End Sub

Private Sub UserForm_Initialize()

Dim i As Integer
Dim lastrow As Integer
Dim varray As Variant
Dim a As Long
lastrow = Cells(Rows.Count, "G").End(xlUp).Row

varray = ActiveSheet.Range("G1:G" & lastrow).Value

With Me.cmbbox1
.ColumnCount = 2
.ColumnWidths = "50,0"
    For a = 1 To UBound(varray, 1)
        
        If varray(a, 1) = "RETURNED" Or varray(a, 1) = "LOST" Or varray(a, 1) = "UNKNOWN" Or _
        varray(a, 1) = "COLLECTION" Or varray(a, 1) = "RECEIVED" Or varray(a, 1) = "NO DATE" Then
            .AddItem varray(a, 1)
            .List(.ListCount - 1, 1) = a
        Else
        End If
    Next
End With
        
    

End Sub

 

[mikerickson]

Code like this might do what you want

Private Sub CommandButton1_Click()
    With ListBox1
        If .ListIndex <> -1 Then
            Application.Goto Range(ListBox1.List(ListBox1.ListIndex, 1))
        End If
    End With
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    Dim oneCell As Range
    ListBox1.ColumnCount = 2
    With ActiveSheet.Range("G:G")
        For Each oneCell In Range(.Cells(8, 1), .Cells(Rows.Count, 1).End(xlUp))
            If IsNumeric(Application.Match(oneCell.Value, Array("RETURNED", "LOST", "UNKNOWN", "COLLECTION", "RECEIVED", "NO DATE"), 0)) Then
                ListBox1.AddItem oneCell.Value
                ListBox1.List(ListBox1.ListCount - 1, 1) = oneCell.Address
            End If
        Next oneCell
    End With
End Sub

 

[ipbr21054]

Looking at this code i get a RTE 424
Object required

Private Sub ComboBox1_Click()

row_index = Me.ComboBox1.Column(1)

ActiveSheet.Range("G" & row_index).Select


End Sub

Private Sub UserForm_Initialize()

Dim i As Integer
Dim lastrow As Integer
Dim varray As Variant
Dim a As Long
lastrow = Cells(Rows.Count, "G").End(xlUp).Row

varray = ActiveSheet.Range("G1:G" & lastrow).Value

With ComboBox1
.ColumnCount = 2
.ColumnWidths = "50,0"
    For a = 1 To UBound(varray, 1)
        
        If varray(a, 1) = "RETURNED" Or varray(a, 1) = "LOST" Or varray(a, 1) = "UNKNOWN" Or _
        varray(a, 1) = "COLLECTION" Or varray(a, 1) = "RECEIVED" Or varray(a, 1) = "NO DATE" Then
            .AddItem varray(a, 1)
            .List(.ListCount - 1, 1) = a
        Else
        End If
    Next
End With
        
    

End Sub

 

[tico_ocit]

But you can initialize the userform? That must be that is trying to access an array position that doesn't exists ..