Advice for simple code please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,817
Office Version
  1. 2007
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,916
It sounds like you are duplicating the functionality of AutoFilter or Find.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,817
Office Version
  1. 2007
Platform
  1. Windows
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,916
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,817
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

tico_ocit

Board Regular
Joined
Apr 5, 2019
Messages
95

ADVERTISEMENT

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.

VBA Code:
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,916
Code like this might do what you want
VBA Code:
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,817
Office Version
  1. 2007
Platform
  1. Windows
Looking at this code i get a RTE 424
Object required


Rich (BB code):
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

Board Regular
Joined
Apr 5, 2019
Messages
95
But you can initialize the userform? That must be that is trying to access an array position that doesn't exists ..
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,550
Members
414,316
Latest member
ExcelLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top