Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter list and display data.
I have the following code that will display data from a list index.
How can I amend or add to the data and save it in the same row as the existing data. Private Sub CommandButton2_Click() Dim FoundCell As Range If Me.ComboBox1.ListIndex = -1 Then 'nothing filled in Beep Exit Sub End If With Worksheets("Customers").Range("A:A") Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ SearchOrder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) End With If FoundCell Is Nothing Then 'this shouldn't happen! Beep Else Me.TextBox1.Value = FoundCell.Offset(0, 0).Value Me.TextBox2.Value = FoundCell.Offset(0, 1).Value Me.TextBox3.Value = FoundCell.Offset(0, 2).Value Me.TextBox4.Value = FoundCell.Offset(0, 3).Value Me.TextBox5.Value = FoundCell.Offset(0, 4).Value Me.TextBox6.Value = FoundCell.Offset(0, 5).Value Me.TextBox7.Value = FoundCell.Offset(0, 6).Value Me.TextBox8.Value = FoundCell.Offset(0, 7).Value Me.TextBox9.Value = FoundCell.Offset(0, 8).Value Me.TextBox10.Value = FoundCell.Offset(0, 9).Value Me.TextBox11.Value = FoundCell.Offset(0, 10).Value Me.TextBox12.Value = FoundCell.Offset(0, 11).Value Me.TextBox13.Value = FoundCell.Offset(0, 12).Value Me.TextBox14.Value = FoundCell.Offset(0, 13).Value Me.TextBox15.Value = FoundCell.Offset(0, 14).Value Me.TextBox16.Value = FoundCell.Offset(0, 16).Value Me.TextBox17.Value = FoundCell.Offset(0, 18).Value Me.TextBox18.Value = FoundCell.Offset(0, 15).Value Me.TextBox19.Value = FoundCell.Offset(0, 19).Value Me.TextBox20.Value = FoundCell.Offset(0, 17).Value Me.CheckBox1.Value = CBool(LCase(FoundCell.Offset(0, 20).Value) = "yes") Me.CheckBox2.Value = CBool(LCase(FoundCell.Offset(0, 21).Value) = "yes") Me.CheckBox3.Value = CBool(LCase(FoundCell.Offset(0, 22).Value) = "yes") Me.CheckBox4.Value = CBool(LCase(FoundCell.Offset(0, 23).Value) = "yes") Me.CheckBox5.Value = CBool(LCase(FoundCell.Offset(0, 24).Value) = "yes") Me.CheckBox6.Value = CBool(LCase(FoundCell.Offset(0, 25).Value) = "yes") Me.CheckBox7.Value = CBool(LCase(FoundCell.Offset(0, 26).Value) = "yes") Me.CheckBox8.Value = CBool(LCase(FoundCell.Offset(0, 27).Value) = "yes") Me.CheckBox9.Value = CBool(LCase(FoundCell.Offset(0, 28).Value) = "yes") Me.CheckBox10.Value = CBool(LCase(FoundCell.Offset(0, 29).Value) = "yes") Me.CheckBox11.Value = CBool(LCase(FoundCell.Offset(0, 30).Value) = "yes") Me.CheckBox12.Value = CBool(LCase(FoundCell.Offset(0, 31).Value) = "yes") Me.CheckBox13.Value = CBool(LCase(FoundCell.Offset(0, 33).Value) = "i agree") Me.CheckBox14.Value = CBool(LCase(FoundCell.Offset(0, 34).Value) = "i disagree") Me.ComboBox2.Value = FoundCell.Offset(0, 32).Value Me.TextBox23.Value = FoundCell.Offset(0, 35).Value 'and on and on for 35 textboxes 'or that looping code from before if your textboxes are nicely named End If End Sub -- law |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i filter data on one worksheet and display on another. | Excel Discussion (Misc queries) | |||
filters how do i keep the display list empty until filter applied | Excel Discussion (Misc queries) | |||
DATA FILTER ARROWS DISPLAY WHILE INACTIVE | Excel Worksheet Functions | |||
filter data from 10+workbooks and display in separate workbook? | Excel Discussion (Misc queries) | |||
Data Filter - Not all rows in spreadsheet will display in Autofilt | Excel Worksheet Functions |