MN2012
08-15-2012, 09:42 PM
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!
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!