Help with look up and row paste

[jedibrown]

Hi,

Basically, I am creating some data and I am a bit stuck.

I would like to say:

If any cells in column c have the word "Giant" in it (there will be a sentance in each cell but I would like it to pick out the specific word) - Then please paste the whole line into sheet 2.

Is this possible?

Many thanks

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Not IsError(Range("C" & i)) Then
        If InStr(Range("C" & i).Value, "giant") > 0 Then Rows(i).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If
Next i
End Sub

 

[jedibrown]

Ok - Tried it and only 1 line is being pasted but should be 36.

Sorry for being difficult!

 

[VoG]

Do you have data in column A?

 

[jedibrown]

No I don't - I have deleted column a and changed the code to look at column "b".

Works better - now 12 are being pasted but still should be 36

 

[VoG]

Perhaps

Sub test()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Not IsError(Range("B" & i)) Then
        If InStr(LCase(Range("B" & i).Value), "giant") > 0 Then Rows(i).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If
Next i
End Sub

 

[jedibrown]

Yes!!! It works!!! Thank you so much for all of your help - It is VERY much appreciated!

Just 2 last things - How do I now attach it to a control button?

Also, is there any way to add another variable to it? So it looks for "giant" and "large".

Sorry to be a pain - I bet you wish you hadn't started helping me now!

 

[VoG]

Try

Private Sub CommandButton1_Click()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Not IsError(Range("B" & i)) Then
        If InStr(LCase(Range("B" & i).Value), "giant") > 0 Or InStr(LCase(Range("B" & i).Value), "large") > 0 Then Rows(i).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If
Next i
End Sub

 

[jedibrown]

Thanks - That doesn't seem to be picking up the "large" as well.

 

[VoG]

It does for me - I actually tested that

 

[jedibrown]

Hmmm, would it be because there is an & in the variable (you may have guessed that giant and large are not the actual things I'm looking for but I am testing before I build).

I am actually looking for P&L. When I change the first variable to P&L and the second to breach - nothing comes up at all yet there are 4 cells with P&L in them.