Apologies for Cross Posting, the post has now been closed,the cross post is here http://www.ozgrid.com/forum/showthread.php?t=86165
It was suggested that I use:
With wksPartsData
Set rng = .Range("a1", .Range("a65536").End(xlUp))
End With
However when using this I still get an error (run time error 91). This is the Code that is highlighted as the problem:
rFilter.AutoFilter Field:=1, Criteria1:=strFind
The full Code is:
Sub cmbFindAll_Click()
Dim strFind As String 'what to find
Dim rFilter As Range 'range to search
With wksPartsData
Set rng = .Range("a1", .Range("a65536").End(xlUp))
End With
strFind = Me.TextBox1.Value
With wksPartsData
If Not .AutoFilterMode Then .Range("A2").AutoFilter
rFilter.AutoFilter Field:=1, Criteria1:=strFind
Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
Me.ListBox1.Clear
For Each c In rng
With Me.ListBox1
.AddItem c.Value
.List(.ListCount - 1, 0) = c.Offset(0, 5).Value
.List(.ListCount - 1, 1) = c.Offset(0, 6).Value
.List(.ListCount - 1, 2) = c.Offset(0, 7).Value
.List(.ListCount - 1, 3) = c.Offset(0, 8).Value
End With
Next c
End With
End Sub
If any has any suggestions I would REALLY appreciate it. I have literally spent hours on this.
I do not necessary need a filter. I would like it to first match what is in text box 1 within the range (the range is on sheet “wksPartData” in cells A:O) and then return the results from the search into list box 1, ideally the search would only return results where column O is greater 40 in value.
Bookmarks