How to search for 2 or more words in a column and if equals - move to different sheet

[danacton]

Hi, it's been a long time and my credentials still work. (and I still remembered them).

I have a spreadsheet that works (a hack job because I don't understand code very well)
I found this code online, some of it I get, other parts I don't.
In my test code I search for a value in column 'C' and if that value = john, the entire row is moved to sheet2 (the job is done)

My goal is to be able to search for more than one value. I would to search for john and dan in column see and if either of those conditions are met I would like the entirerow to move to sheet2. I've spent a lot of time repeating functions and looking for solutions - thought it was time to ask the experts.

Here's what I have - and please excuse me if I have extraneous in this code, but it does work with one name: john. I would also like it to NOT be case sensitive if possible...

Sub Cheezy()
'Updated by Kutools for Excel 2017/8/28
    Dim xRg As Range
    Dim xCell As Range
    Dim I As Long
    Dim J As Long
    Dim K As Long
    I = Worksheets("Sheet1").UsedRange.Rows.Count
    J = Worksheets("Sheet2").UsedRange.Rows.Count
    If J = 1 Then
       If Application.WorksheetFunction.CountA(Worksheets("Sheet2").UsedRange) = 0 Then J = 0
    End If
    Set xRg = Worksheets("Sheet1").Range("C1:C" & I)
    On Error Resume Next
    Application.ScreenUpdating = False
    For K = 1 To xRg.Count
        If CStr(xRg(K).Value) = "john" Then
            xRg(K).EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & J + 1)
            xRg(K).EntireRow.Delete
            If CStr(xRg(K).Value) = "john" Then
                K = K - 1
            End If
            J = J + 1
        End If
    Next
    Application.ScreenUpdating = True
End Sub

This sub is called in sheet1 where you right click the bottom tab and add code (General)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C2:C100")) Is Nothing Then
    If Target.Value = "Done" Then
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        'Target.ClearContents
        Call Cheezy
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    Else
    End If
    End If
End Sub

Thanks ahead for any help in getting 2 or more names I can search for.

 

[My Aswer Is This]

Try this

Run this script from Sheet(1)

Script looks down Column(C) for dan and john
If found row is copied to Sheet(2)

Sub Filter_Me_Please_Array()
'Modified  2/15/2019  11:22:29 PM  EST
Dim Del As Variant
Del = Array("john", "dan") ' Modify search values
Dim lastrow As Long
Dim c As Long
c = 3
lastrow = Cells(Rows.Count, c).End(xlUp).Row
With ActiveSheet.Cells(1, c).Resize(lastrow)
    .AutoFilter 1, Del, Operator:=xlFilterValues
    counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(2).Rows(1)
 
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
End Sub

 

[My Aswer Is This]

Where you see this:

Del = Array("john", "dan") ' Modify search values

Just add more if you want like this:

Del = Array("john", "dan","Car","Cat","Bird") ' Modify search values
​

_{^{<strike>
</strike>}}​

 

[danacton]

Thank you and thanks for the quick reply.

I tested the code in my sample and it works and appears to be very elegant. I really don't get how your code works so I will just ask for a couple of tweaks:
1. My code actually deletes the row with 'john' in it and adds it to the top of sheet2 - can you make that work in this code?
2. My macro runs as soon as 'john' finishes his entry whereas I had to manually run your macro (maybe I put it in the wrong place - I clicked sheet 1 and pasted it in the 'general' code area). Can you make this macro run as soon as the names are entered?

The goal is that I will have 2 men doing tasks out of this spreadsheet and as soon as they sign off on a particular task (column C) the task goes away and is archived in order on sheet2 - which will ultimately have a name like "Job Archive". None of these guys will know how to run a macro. Sorry I'm so ignorant here - I am grateful for your help!

 

[My Aswer Is This]

Well I never try to read other peoples code which is not doing what they want and try to modify it to do what they want.

I thought you said if value in column C equals Jon or dan copy row to other sheet

And I did not understand you wanted to have this done automatically and then delete the row from the original sheet.

So when you enter joh or dan in column C you immediately want this row copied to sheet(2)
This is sort of a dangerous practice in my opinion.
If you enter joh in column C and then wanted to enter something else in column D your script would have already run and the row in the current sheet would be deleted.

And any time you enter any value in column C the script is going to try to run but then it realizes you did not enter Jon or dan the script stops and does nothing.
So this is causing a whole lot of performance

I suggest you double click on column C if you want the row copied. But hey I just do what you ask for.
If your users are unable to run macros by clicking on a button just hope they do not enter Jon by mistake.

There is no undo when running Macros.


Are you entering these values like Joh and dan manually or as a result of a formula?

So your set on you ideal ??

 

[danacton]

Yes sir, if you would be so kind we still would like to delete the row and add it to sheet2.

The operator(s) will not be adding anything to the row except his name that he completed the task. They are already doing this and we haven't had any issues for the last 2 months other than there are so many rows of data that it's becoming cumbersome for them. if they had to refer to a job (row) that was moved - they could easily do so.

They are manually adding in their names when they've completed the task.

 

[danacton]

What if - it were a select box where 'john' and 'dan' were the only options for entry?

 

[My Aswer Is This]

Well you could use a data validation list.

Where only john or dan could be entered

Now you said more then Jon or dan may be what you want

So we would need to put in the script jon or dan or Mary or Dog or cat or fish

How would the script know what values to look for

So is this what you want if you enter jon of dan you want this row copied to sheet 2 and deleted from sheet 1

Is that correct?

 

[danacton]

Yes sir, that would be perfect, it is possible that we would need more than Jon or Dan, so yes if we could put the rest in there as well - or as long as I could add them later, I can do that.

Yes, if it was copied to sheet 2 and deleted from sheet 1 - perfect.

Thanks

 

[My Aswer Is This]

Try this:

Add more words if you want.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  2/17/2019  2:10:35 AM  EST
If Target.Column = 3 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Application.EnableEvents = False
Dim Lastrow As Long
Lastrow = Sheets(2).Cells(Rows.Count, "C").End(xlUp).Row + 1
Dim ans As Long
ans = Target.Row
Select Case Target.Value
   Case "Jon", "dan"  ' Add more here if you want
        Rows(ans).Copy Sheets(2).Rows(Lastrow)
        Rows(ans).Delete
End Select
End If
Application.EnableEvents = True
End Sub