textbox search for listbox with an array

[Wornout8]

I have a userform with a list box " lstLookup2" and a textbox "txt21"
My data is on a worksheet called "Data" it has columns A to R
I know I have to use an array to fill the list box because it has more than 10 columns but I know nothing about arrays
The txt21 will have a value in it which is in column Q
I want to look up column Q and return all rows that contain that value all columns
For 2 days I have been trying heaps of code but I could get nothing to work in the end I gave up and decided to ask and now I have no code at all
can somebody help me please

[kev_]

There are several ways to achieve what you want.
This is my simplistic approach:

When txt21 changes
- apply auto-filter to values in sheet "Data" (with criteria = txt21 value)
- copy filtered values to sheet "FilteredList"
- assign "FilteredList" values to listbox

test in attached workbook
- enter A,B or C in textbox

here is the code:

Please Login or Register  to view this content.

It is always best to start simple, but if your filtering requirements are more complex, let me know

Notes
CurrentRegion is used 3 times in the code
- CurrentRegion is the contiguous range around any cell or range
- each current region is bordered by the first blank row/column around any table of data
- so do not leave column/row empty to make things look pretty!

.Range("A1").CurrentRegion.Copy rng
- copies the filtered data
- pastes to rng
- rng was set previously as cell A1 on "FilteredList"


EDIT - spotted a typo - oops!
It still works in this instance, but D1 should of course be R1

Please Login or Register  to view this content.

Please amend the VBA in the attached workbook

[Wornout8]

I am wondering if you could help me with the next problem
I have cmdAdd2 on the form along with cmdEdi2, cmdDelet2e cmdReset2
but right now I am trying to get the add button to work.
This is a multi page form and this is page 2 all the controls work on page 1
so I asumed that If I copied the code and renamed the cmd buttons with a 2 then they would work I put the clear filter in because the datasheet was still filtered from the code you gave me and I thought that was throwing things out.
here is the code I have txt11 is a number that has to be entered
it is supposed to put in the data from the txtboxs on the form into the sheet but debugs and says could not find specified object

Please Login or Register  to view this content.

[kev_]

Away from PC - so cannot test.

It's a good habit to declare all variables including type
- you have omitted to do that with most of them
- VBA is able to help more if it is told what each variable should hold

nextrow is correctly Set as a range on 2 lines (as declared)
But this line
nextrow = Me.Controls("txt" & x).Value
Do you mean
nextrow.Value = Me.Controls("txt" & x).Value

Which line is highlighted when the code fails?