Yet another Listbox question...
 

Hi All
I have been trying to modify my code below so that each time
the value in Textbox1 is found, instead of sending the the data found
in columns (A,B,C,D) of that row to 4 different Textboxes and having
to press ok in the msgbox to find the next matching value, I would
like to send the entire row of 4 columns of every match to a listbox
as it cycles through the columnA looking for matches..

I have found several pieces of code in this news group that would help
but not being a very good at programming have failed to integrate them
with any success. Although I have been able to generate several
different error messages) :)


Private Sub CommandButton1_Click()
i = 2

Do While Cells(i, 1).Value < ""


If TextBox1.Value = Cells(i,1).Value Then

TextBox2.Value = Cells(i, 1).Value 'columnA
TextBox3.Value = Cells(i, 2).Value 'columnB
TextBox4.Value = Cells(i, 3).Value 'columnC
TextBox5.Value = Cells(i, 4).Value 'columnD

MsgBox "Next Record"

End If

i = i + 1
Loop


End Sub



Any help would be appreciated

Pixel

Yet another Listbox question...
 

How about something like:

Option Explicit
Private Sub CommandButton1_Click()

Dim myCell As Range
Dim myRng As Range
Dim iCtr As Long

With Worksheets("sheet1")
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

If Trim(Me.TextBox1.Text) = "" Then
Beep
Exit Sub
End If

Me.ListBox1.Clear
For Each myCell In myRng.Cells
If LCase(myCell.Value) = LCase(Me.TextBox1.Text) Then
With Me.ListBox1
.AddItem myCell.Value
For iCtr = 1 To Me.ListBox1.ColumnCount - 1
.List(.ListCount - 1, iCtr) = myCell.Offset(0, iCtr).Value
Next iCtr
End With
End If
Next myCell

End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Me.ListBox1.ColumnCount = 4
End Sub


Pixel wrote:

Hi All
I have been trying to modify my code below so that each time
the value in Textbox1 is found, instead of sending the the data found
in columns (A,B,C,D) of that row to 4 different Textboxes and having
to press ok in the msgbox to find the next matching value, I would
like to send the entire row of 4 columns of every match to a listbox
as it cycles through the columnA looking for matches..

I have found several pieces of code in this news group that would help
but not being a very good at programming have failed to integrate them
with any success. Although I have been able to generate several
different error messages) :)

Private Sub CommandButton1_Click()
i = 2

Do While Cells(i, 1).Value < ""


If TextBox1.Value = Cells(i,1).Value Then

TextBox2.Value = Cells(i, 1).Value 'columnA
TextBox3.Value = Cells(i, 2).Value 'columnB
TextBox4.Value = Cells(i, 3).Value 'columnC
TextBox5.Value = Cells(i, 4).Value 'columnD

MsgBox "Next Record"

End If

i = i + 1
Loop


End Sub

Any help would be appreciated

Pixel

--

Dave Peterson

Yet another Listbox question...
 

Wow!! How about something like that.. It worked like a charm I'm
embarassed to tell you how many days I've played with this trying to
get it to work in fact it was even some of your code from previous
posts that I tried to integrate....

Thank you very much for your help

pixel



On Wed, 18 Jan 2006 15:07:36 -0600, Dave Peterson
wrote:

How about something like:

Option Explicit
Private Sub CommandButton1_Click()

Dim myCell As Range
Dim myRng As Range
Dim iCtr As Long

With Worksheets("sheet1")
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

If Trim(Me.TextBox1.Text) = "" Then
Beep
Exit Sub
End If

Me.ListBox1.Clear
For Each myCell In myRng.Cells
If LCase(myCell.Value) = LCase(Me.TextBox1.Text) Then
With Me.ListBox1
.AddItem myCell.Value
For iCtr = 1 To Me.ListBox1.ColumnCount - 1
.List(.ListCount - 1, iCtr) = myCell.Offset(0, iCtr).Value
Next iCtr
End With
End If
Next myCell

End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Me.ListBox1.ColumnCount = 4
End Sub


Pixel wrote:

Hi All
I have been trying to modify my code below so that each time
the value in Textbox1 is found, instead of sending the the data found
in columns (A,B,C,D) of that row to 4 different Textboxes and having
to press ok in the msgbox to find the next matching value, I would
like to send the entire row of 4 columns of every match to a listbox
as it cycles through the columnA looking for matches..

I have found several pieces of code in this news group that would help
but not being a very good at programming have failed to integrate them
with any success. Although I have been able to generate several
different error messages) :)

Private Sub CommandButton1_Click()
i = 2

Do While Cells(i, 1).Value < ""


If TextBox1.Value = Cells(i,1).Value Then

TextBox2.Value = Cells(i, 1).Value 'columnA
TextBox3.Value = Cells(i, 2).Value 'columnB
TextBox4.Value = Cells(i, 3).Value 'columnC
TextBox5.Value = Cells(i, 4).Value 'columnD

MsgBox "Next Record"

End If

i = i + 1
Loop


End Sub

Any help would be appreciated

Pixel

Yet another Listbox question...
 

If you found that code via google, it's always better to sort by date--toss the
old, old stuff <vbg.

Pixel wrote:

Wow!! How about something like that.. It worked like a charm I'm
embarassed to tell you how many days I've played with this trying to
get it to work in fact it was even some of your code from previous
posts that I tried to integrate....

Thank you very much for your help

pixel

On Wed, 18 Jan 2006 15:07:36 -0600, Dave Peterson
wrote:

How about something like:

Option Explicit
Private Sub CommandButton1_Click()

Dim myCell As Range
Dim myRng As Range
Dim iCtr As Long

With Worksheets("sheet1")
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

If Trim(Me.TextBox1.Text) = "" Then
Beep
Exit Sub
End If

Me.ListBox1.Clear
For Each myCell In myRng.Cells
If LCase(myCell.Value) = LCase(Me.TextBox1.Text) Then
With Me.ListBox1
.AddItem myCell.Value
For iCtr = 1 To Me.ListBox1.ColumnCount - 1
.List(.ListCount - 1, iCtr) = myCell.Offset(0, iCtr).Value
Next iCtr
End With
End If
Next myCell

End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Me.ListBox1.ColumnCount = 4
End Sub


Pixel wrote:

Hi All
I have been trying to modify my code below so that each time
the value in Textbox1 is found, instead of sending the the data found
in columns (A,B,C,D) of that row to 4 different Textboxes and having
to press ok in the msgbox to find the next matching value, I would
like to send the entire row of 4 columns of every match to a listbox
as it cycles through the columnA looking for matches..

I have found several pieces of code in this news group that would help
but not being a very good at programming have failed to integrate them
with any success. Although I have been able to generate several
different error messages) :)

Private Sub CommandButton1_Click()
i = 2

Do While Cells(i, 1).Value < ""


If TextBox1.Value = Cells(i,1).Value Then

TextBox2.Value = Cells(i, 1).Value 'columnA
TextBox3.Value = Cells(i, 2).Value 'columnB
TextBox4.Value = Cells(i, 3).Value 'columnC
TextBox5.Value = Cells(i, 4).Value 'columnD

MsgBox "Next Record"

End If

i = i + 1
Loop


End Sub

Any help would be appreciated

Pixel

--

Dave Peterson