Solved: FindAll from Textbox Search on Form and fill ListBox with 34 columns

[MN2012]

I have a 34 column spreadsheet with a form that does searches from three textboxes and fills the other textboxes on the form based on the search. Two textbox searches work fine. The third, however, from column M, has duplicates in the search and with 34 columns I can't use additem. I set up a FindAll for that search from code I found on one of these wonderful help sites:

Sub FindAll()
Dim arrData() As Variant
Dim strFind As String 'what to find
Dim rFilter As Range 'range to search
Dim Rw As Range
Dim Cell As Range
Dim ColCnt As Long
Dim RowCnt As Long
Me.ListBox1.Clear
ListBox1.Visible = True

strFind = Me.TextBox1.Value
Set rFilter = Sheet1.Range("M8").CurrentRegion
With Sheet1

If Not .AutoFilterMode Then .Range("M8").AutoFilter
rFilter.AutoFilter Field:=1, Criteria1:=strFind
Set rng = rFilter.Cells.SpecialCells(xlCellTypeVisible)
For Each Rw In rng.Rows
ColCnt = ColCnt + 1
ReDim Preserve arrData(1 To rng.Columns.Count, 0 To ColCnt)
For Each Cell In Rw.Cells
RowCnt = RowCnt + 1
arrData(RowCnt, ColCnt) = Cell.Value
Next Cell
RowCnt = 0
Next Rw
Me.ListBox1.List = WorksheetFunction.Transpose(arrData)
End With
End Sub

And then it is supposed to populate a ListBox with the results. I can only get it to returm the column headers (in row 7) and one of the results. When I look at the filter it is only showing one row under the header when there should be two rows.

I have tried everything I can think of. ANy help will be greatly appreciated!

[CatDaddy]

Firstly please wrap your code in vba tags (using the green VBA button in the text formatting ribbon) and is this on a Userform? could you post a sample workbook i can have it done in a few minutes

[CatDaddy]

[VBA][/VBA]This worked for me...
In standard module:
[VBA]Sub FindAll()
UserForm1.Show
End Sub[/VBA]
In UserForm1 module:
[VBA]Private Sub TextBox1_Change()
Dim arrData() As Variant
Dim strFind As String 'what to find
Dim rFilter, Rw, Cell, Rng As Range
Dim ColCnt, RowCnt As Long
With Sheet1
Me.ListBox1.Clear
Me.ListBox1.Visible = True
strFind = Me.TextBox1.Value
Set rFilter = .Range("M8").CurrentRegion
If Not .AutoFilterMode Then .Range("M8").AutoFilter
rFilter.AutoFilter Field:=1, Criteria1:=strFind
Set Rng = rFilter.Cells.SpecialCells(xlCellTypeVisible)

For Each Rw In Rng.Rows
ColCnt = ColCnt + 1
ReDim Preserve arrData(1 To Rng.Columns.Count, 0 To ColCnt)

For Each Cell In Rw.Cells
RowCnt = RowCnt + 1
arrData(RowCnt, ColCnt) = Cell.Value
Next Cell
RowCnt = 0
Next Rw

Me.ListBox1.List = WorksheetFunction.Transpose(arrData)
End With
End Sub
[/VBA]

[snb]

one of many approaches:

http://www.snb-vba.eu/VBA_Afhankelij...xen_en.html#L2

[MN2012]

I was so delighted to see responses! Thank you! I have been going crazy with this file. I will get a file cleaned to post. One thing I see I didn't say was that the change event is not on the textbox, I have command buttons next to the textbox. I also vow to remember to properly wrap my code in tags.

[MN2012]

See Attached Sample file.

Column M has dups in it for the search in the textbox next to CommandButton3 (found a quarter of the way down on the form). I put in two numbers with dups in the spreadsheet to test:
1004400000
1004500000

I have a ListBox1 that is supposed to pop up under the textbox after "Ok" in the msg saying that more than one was found.

Then I want it to list all the dups with all their columns and when you choose one the form is populated based on that choice and the ListBox1 disappears.

As an aside, if anyone has a suggestion for getting the form to fit to the size of whatever monitor it is opened in that would be great - I thought I had that problem licked until I opened this at home on my laptop and the form is not resizing.

Thanks so much for all your help! Literally going gray over this.

[CatDaddy]

change:
[VBA]arrData(RowCnt, ColCnt) = Cell.Value[/VBA]
to:
[VBA]arrData(RowCnt, ColCnt) = Cell.Value & " - " & Cell.Column[/VBA]

[MN2012]

I made that change in the FindAll Sub but I am still getting just the column headers and one row of the search results in my ListBox. Any other ideas?

Appreciate the help!

[MN2012]

Thanks CatDaddy! Your first post with the UserForm1 module code didn't work until I played with it enough and finally changed the rFilter = .Range("M8").CurrentRegion to ("A8"), the .Range("M8").AutoFilter to ("A8") and the Field:= to 13. Now it works beautifully. THANKS!