Userforms/Macro Search

[james batley]

Hello,

I have a large spreadsheet with lots of columns. I have
designed a UserForm to Help me select information to look
at. I have categorized the items and can fill list & text
boxes with "stage one" data. There are no two entries the
same.

What I want to do is push a button and populate some list
boxes with "stage two" data. I think that this involves a
search in the first column to find the item and then some
method to bring in the data from the same row but several
columns to the right.

ITEM COL2 COL3 COL4 COL5 INFO1 INFO2


Seach for a specific item then fill box 1 and 2 with INFO1
and INFO2 respectively.

I have spent 5hours today trying to do this and failed
miserably.

Can anyone help???

Thanks James

 

[kkknie]

Should be fairly simple.

1. Get the selected ITEM from the listbox.
2. Search through all items in the first column for the value (assumin
column 1 starting with row 1).
3. When found, set the text boxes to the values in columns F and G (6
7) for the row it was found and then exit.

Code
-------------------

Sub Test()

Dim r as Range
Dim strFind as String

strFind = ListBox1.Text

For Each r in Range("A1:A" & Range("A65535").End(xlUp).Row
If r.Value = strFind Then
TextBox1.Text = Range("F" & r.Row).Value
TextBox2.Text = Range("G" & r.Row).Value
Exit For
End If
Next

End Su

 

[Tom Ogilvy]

And if they are listboxes as you said (rather than textboxes) you would
adjust the code to:


Code:
--------------------

Sub Listbox1_Click()

Dim r as Range
Dim strFind as String
Dim sh as Worksheet
set sh = Worksheets("Data")
strFind = Lcase(ListBox1.Text)

For Each r in Sh.Range("A1:A" & sh.Range("A65535").End(xlUp).Row)
If lcase(r.Value) = strFind Then
ListBox2.AddItem sh.Range("F" & r.Row).Value
ListBox3.AddItem sh.Range("G" & r.Row).Value
End If
Next

End Sub