So I am attempting to secure my sheet with some error capture on a certain cell A10, as this is where users are inputting search terms to filter out data to match their term.
I had a previous error return that would check their entry against every entry in the data sheet to see if there was any match, if not then return an error.
I am looking to do the same for if there is NO/NULL ENTRY in cell A10. Below is what I have tried but I cant seem to get it working.Code
- ''///Make 'x' the value of A10 (search parameter)
- x = Worksheets("search").Range("A10").Value
- ''///Use 'x' to then filter through data sheet
- Set chk = Worksheets("data").Range("A10").CurrentRegion.Columns(1).Cells.Find(x)
- ''///If no matches then return ERROR
- If chk Is Nothing Then
- MsgBox x & " is not in the database", vbCritical, "Try Again"
- Exit Sub
- End If
- If IsEmpty(Range("A10")) Then
- MsgBox "Plesae enter a value.", vbCritical, "Null Entry"
- Exit Sub
- End If
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?
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.
I have just done a random word search and it returns all of the documents, but they do not include the word inputted, for example "moose" returns all documents.
How would I go about implementing a fail safe error capture into this to return null or an error when the word inputted is not found.
I commented that because as from the screenshots posted, the tick in column W did not show in the search (2nd screenshot).
Column W along with Column V didnt seem to have "shifted" down
Hope this makes sense?
That seems to have worked.
Last thing I am looking to do is remove the columns I - U from the final search outcome.
I have tried entering .Columns("I:U").EntireColumn.Delete much like the previous row to remove the beginning section from the search results but that doesnt seem to return the correct results.
How would I achieve this?
The filtered down list after searching "front"
The results of the search where the tick for Column W on "data" sheet has not been copied over
Yep that is close to what I am looking to do.
All ticks do not seem to be showing though. E.G. The first 2 columns are after the document name but further down the columns are not displaying
So ultimately I am after the Name of the Document along with the cells that start to have ticks within them (column G) to only be shown in the Copied data (after the search has been performed)
Sorry for any confusion
The typo sis in another module.
- Sub CopyData()
- Dim rng As Range
- Dim x As String
- x = Worksheets("sheet2").Range("a1").Value
- 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)
- rng.Copy Worksheets("sheet2").Cells(Worksheets("sheet2").Rows.Count, 3).End(xlUp).Offset(1)
- End With
- End Sub
That seems to have sorted the search functionality and supplied me with the data required.
One little tweak that I am also looking to do is to remove rows B, C, D, E and F from the filtered search.
How would I go about implementing this?
I would be faster to do this with Find and FindNext, but faster still would be AutoFilter
I am fairly new into using VBA, how exactly would I achieve this through the current macro I have created or creating a new one?
I am attempting to search the values of Column A on the "data" sheet and return the values from Column A and G Onwards onto Sheet2.
I have the search function somewhat working as it is returning values that contain the inputted search parameter of Column A but seems to be duplicating when it find the search parameter in another column also.
Would really appreciate some help to get me the search results I am looking for.