Hi,
That does indeed bring up an error message but seems to do it for any word you enter, even if it has values to return. E.G. I entered front and it returned the error but should instead populate the table and not return an error.
Thanks
Partial Search on String to return Row
- KDoubleU
- Thread is marked as Resolved.
-
-
-
This should work
Code
Display MoreOption Explicit Sub CopyData() Dim tempSht As Worksheet Dim rng As Range, chk As Range Dim x As String x = Worksheets("sheet2").Range("a1").Value Set chk = Worksheets("data").Range("A1").CurrentRegion.Columns(1).Cells.Find(x) If chk Is Nothing Then MsgBox x & " is not in the database", vbCritical, "Try again" Exit Sub End If Worksheets.add(After:=Worksheets(Worksheets.Count)).Name = "Temp" With Worksheets("data") ''///check whether a filter Is applied If Not .AutoFilterMode Then .Range("A1").AutoFilter .Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:="=*" & x & "*" _ , Operator:=xlAnd ''///set a range = to visible cells (excluding the header) Set rng = .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows.Count - 1) With ActiveSheet rng.Copy.Range ("A1") .Columns("B:F").EntireColumn.Delete .Range("A1").Resize(.Cells(.Rows.Count, 1).End(xlUp).Row, 19).Copy Worksheets("sheet2").Cells(Worksheets("sheet2").Rows.Count, 3).End(xlUp).Offset(1) Application.DisplayAlerts = False .Delete Application.DisplayAlerts = True End With End With End Sub -
Hi,
Yep that seems to work a lot better.
Noticed 1 thing though, I am having to go into the data tab to reset the filter each tome, otherwise it only searches on the filtered down data.
E.G I search Front, data sheet filters down to only show Front. I reset and then search Blood, doesn't find it as only Front is filtered down still.
How do I reset the data sheet filter to show all data again on reset?
-
I've added code to clear the AutoFilter and made a couple of slight changes to improve the code
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!