I have a worksheet LOADED w/ info; I have created a UserForm with a textbox,
commadButton and a listbox. When i type a criteria in the textbox in the
information appears in the list box. After a good while I notice more the one
occurrence of said criteria how do I modify the code below to include the
second occurrence in the list box?

Sub CommandButton1_Click()
Dim rng As Range
Dim Ary(6) As String
Dim i As Single

If Me.TextBox1.Value = "" Then
MsgBox "You Must Enter A Number", _
vbExclamation, "Error"
Exit Sub
End If
Set rng = Worksheets(1).Range("A2:A65536").Find _
(Me.TextBox1.Value, LookIn:=xlValues)
If Not rng Is Nothing Then
rng.Select
ListBox1.ColumnCount = 6

For i = 0 To 6
Ary(0) = i
Next i

Ary(0) = ActiveCell.Value
Ary(1) = ActiveCell.Offset(0, 1).Value
Ary(2) = ActiveCell.Offset(0, 2).Value
Ary(3) = ActiveCell.Offset(0, 3).Value
Ary(4) = ActiveCell.Offset(0, 4).Value
Ary(5) = ActiveCell.Offset(0, 5).Value
ListBox1.Column() = Ary
Else
MsgBox Me.TextBox1.Value & _
" Does Not Exist", vbCritical, "Error"
Exit Sub
End If
Me.TextBox1.Value = ""
End Sub

I dropped the array stuff and just plopped the values into the listbox:

As a user, I'd rather see a not in a label than have to dismiss a msgbox.

Option Explicit
Sub CommandButton1_Click()

Dim FoundCell As Range
Dim FirstAddress As String
Dim wks As Worksheet
'Dim i As Long 'don't use Single and don't use Integer

Me.Label1.Caption = ""

With Me.TextBox1
If .Value = "" Then
Me.Label1.Caption = "You Must Enter A Number"
.SetFocus
Exit Sub
End If
End With

Set wks = Worksheets(1)

With wks
With .Range("a2:A" & .Rows.Count)
Set FoundCell = .Cells.Find(What:=Me.TextBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext)

If FoundCell Is Nothing Then
With Me.TextBox1
Me.Label1.Caption = .Value & "--Not found"
.SetFocus
.Value = ""
End With
Exit Sub
End If

FirstAddress = FoundCell.Address

Do
With Me.ListBox1
.AddItem FoundCell.Value
.List(.ListCount - 1, 1) = FoundCell.Offset(0, 1).Value
.List(.ListCount - 1, 2) = FoundCell.Offset(0, 2).Value
.List(.ListCount - 1, 3) = FoundCell.Offset(0, 3).Value
.List(.ListCount - 1, 4) = FoundCell.Offset(0, 4).Value
.List(.ListCount - 1, 4) = FoundCell.Offset(0, 5).Value
End With

Set FoundCell = .FindNext(after:=FoundCell)

If FoundCell Is Nothing Then
Exit Do
End If

If FoundCell.Address = FirstAddress Then
Exit Do
End If
Loop
End With
End With

Me.TextBox1.Value = ""
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()

'initial your controls in the _initialize event
With Me.ListBox1
.ColumnCount = 6
.MultiSelect = fmMultiSelectSingle '???
End With

With Me.CommandButton1
.Caption = "Ok"
.TakeFocusOnClick = False
.Default = True
End With

With Me.CommandButton2
.Caption = "Cancel"
.TakeFocusOnClick = False
.Cancel = True
End With

With Me.TextBox1
.Value = ""
.SetFocus
End With

With Me.Label1
.Caption = ""
.ForeColor = &HFF
End With

End Sub



Office_Novice wrote:

I have a worksheet LOADED w/ info; I have created a UserForm with a textbox,
commadButton and a listbox. When i type a criteria in the textbox in the
information appears in the list box. After a good while I notice more the one
occurrence of said criteria how do I modify the code below to include the
second occurrence in the list box?

Sub CommandButton1_Click()
Dim rng As Range
Dim Ary(6) As String
Dim i As Single

If Me.TextBox1.Value = "" Then
MsgBox "You Must Enter A Number", _
vbExclamation, "Error"
Exit Sub
End If
Set rng = Worksheets(1).Range("A2:A65536").Find _
(Me.TextBox1.Value, LookIn:=xlValues)
If Not rng Is Nothing Then
rng.Select
ListBox1.ColumnCount = 6

For i = 0 To 6
Ary(0) = i
Next i

Ary(0) = ActiveCell.Value
Ary(1) = ActiveCell.Offset(0, 1).Value
Ary(2) = ActiveCell.Offset(0, 2).Value
Ary(3) = ActiveCell.Offset(0, 3).Value
Ary(4) = ActiveCell.Offset(0, 4).Value
Ary(5) = ActiveCell.Offset(0, 5).Value
ListBox1.Column() = Ary
Else
MsgBox Me.TextBox1.Value & _
" Does Not Exist", vbCritical, "Error"
Exit Sub
End If
Me.TextBox1.Value = ""
End Sub

--

Dave Peterson

....I'd rather see a NOTE in a label...

Dave Peterson wrote:

<<snipped

Nicely done sir thank you, I see what you meant by the note beig a little
more user friendly.Thanks for the tip.

"Dave Peterson" wrote:

I dropped the array stuff and just plopped the values into the listbox:

As a user, I'd rather see a not in a label than have to dismiss a msgbox.

Option Explicit
Sub CommandButton1_Click()

Dim FoundCell As Range
Dim FirstAddress As String
Dim wks As Worksheet
'Dim i As Long 'don't use Single and don't use Integer

Me.Label1.Caption = ""

With Me.TextBox1
If .Value = "" Then
Me.Label1.Caption = "You Must Enter A Number"
.SetFocus
Exit Sub
End If
End With

Set wks = Worksheets(1)

With wks
With .Range("a2:A" & .Rows.Count)
Set FoundCell = .Cells.Find(What:=Me.TextBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext)

If FoundCell Is Nothing Then
With Me.TextBox1
Me.Label1.Caption = .Value & "--Not found"
.SetFocus
.Value = ""
End With
Exit Sub
End If

FirstAddress = FoundCell.Address

Do
With Me.ListBox1
.AddItem FoundCell.Value
.List(.ListCount - 1, 1) = FoundCell.Offset(0, 1).Value
.List(.ListCount - 1, 2) = FoundCell.Offset(0, 2).Value
.List(.ListCount - 1, 3) = FoundCell.Offset(0, 3).Value
.List(.ListCount - 1, 4) = FoundCell.Offset(0, 4).Value
.List(.ListCount - 1, 4) = FoundCell.Offset(0, 5).Value
End With

Set FoundCell = .FindNext(after:=FoundCell)

If FoundCell Is Nothing Then
Exit Do
End If

If FoundCell.Address = FirstAddress Then
Exit Do
End If
Loop
End With
End With

Me.TextBox1.Value = ""
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()

'initial your controls in the _initialize event
With Me.ListBox1
.ColumnCount = 6
.MultiSelect = fmMultiSelectSingle '???
End With

With Me.CommandButton1
.Caption = "Ok"
.TakeFocusOnClick = False
.Default = True
End With

With Me.CommandButton2
.Caption = "Cancel"
.TakeFocusOnClick = False
.Cancel = True
End With

With Me.TextBox1
.Value = ""
.SetFocus
End With

With Me.Label1
.Caption = ""
.ForeColor = &HFF
End With

End Sub



Office_Novice wrote:

I have a worksheet LOADED w/ info; I have created a UserForm with a textbox,
commadButton and a listbox. When i type a criteria in the textbox in the
information appears in the list box. After a good while I notice more the one
occurrence of said criteria how do I modify the code below to include the
second occurrence in the list box?

Sub CommandButton1_Click()
Dim rng As Range
Dim Ary(6) As String
Dim i As Single

If Me.TextBox1.Value = "" Then
MsgBox "You Must Enter A Number", _
vbExclamation, "Error"
Exit Sub
End If
Set rng = Worksheets(1).Range("A2:A65536").Find _
(Me.TextBox1.Value, LookIn:=xlValues)
If Not rng Is Nothing Then
rng.Select
ListBox1.ColumnCount = 6

For i = 0 To 6
Ary(0) = i
Next i

Ary(0) = ActiveCell.Value
Ary(1) = ActiveCell.Offset(0, 1).Value
Ary(2) = ActiveCell.Offset(0, 2).Value
Ary(3) = ActiveCell.Offset(0, 3).Value
Ary(4) = ActiveCell.Offset(0, 4).Value
Ary(5) = ActiveCell.Offset(0, 5).Value
ListBox1.Column() = Ary
Else
MsgBox Me.TextBox1.Value & _
" Does Not Exist", vbCritical, "Error"
Exit Sub
End If
Me.TextBox1.Value = ""
End Sub

--

Dave Peterson