Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms/Macro Search
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms/Macro Search
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 ------------------- -- kkkni ----------------------------------------------------------------------- kkknie's Profile: http://www.excelforum.com/member.php...nfo&userid=754 View this thread: http://www.excelforum.com/showthread.php?threadid=26290 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms/Macro Search
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 -------------------- -- Regards, Tom Ogilvy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search Macro | Excel Worksheet Functions | |||
Macro for Search | Excel Discussion (Misc queries) | |||
Search Macro | Excel Discussion (Misc queries) | |||
Excel XP VBA code to search all macro code in Excel module for specific search string criteria | Excel Programming | |||
Excel XP VBA code to search all macro code in Excel module for specific search string criteria | Excel Programming |