Posts by KDoubleU

Full Workbook

Hi,

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.

    ''///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"
        Worksheets("search").Activate
        Exit Sub
    End If
    
    If IsEmpty(Range("A10")) Then
        MsgBox "Plesae enter a value.", vbCritical, "Null Entry"
        Exit Sub
    End If

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?

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

Hi,

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.

Indeed.

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?

Thanks,

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"

and

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

Yes.

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

Quote from royUK

I'm not sure what you mean Rows are numbers, columns are letters.

Sorry! I mean columns.

I wish to have the columns B, C, D, E and F removed from the search as the info isnt required

Quote from royUK

The typo sis in another module.

Try this

Code

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

Display More

Thanks Roy,

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?

Quote from royUK

I would be faster to do this with Find and FindNext, but faster still would be AutoFilter

Thanks Roy,

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?

Hi,

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.