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

 

[jedibrown]

Although now this won't work

Private Sub CommandButton1_Click()
Dim LR As Long, i As Long
Sheets("Sheet5").UsedRange.ClearContents
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If Not IsError(Range("b" & i)) And Not IsError(Range("G" & i)) Then
If (LCase(Range("C" & i).Value) = "incident" Or LCase(Range("C" & i).Value) = "problem") And _
LCase(Range("c" & i).Value) <> "complete" And _
LCase(Range("c" & i).Value) <> "rejected" And _
LCase(Range("c" & i).Value) <> "testing" And _
LCase(Range("c" & i).Value) <> "fixed" And _
LCase(Range("c" & i).Value) <> "signoff" And _
LCase(Range("c" & i).Value) <> "release" _
Then Rows(i).Copy Destination:=Sheets("Sheet5").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub

 

[jedibrown]

Just spotted the problem - put c instead of b!

 

[VoG]

Maybe

Private Sub CommandButton1_Click()
Dim LR As Long, i As Long
Sheets("Sheet5").UsedRange.ClearContents
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Not IsError(Range("C" & i)) And Not IsError(Range("G" & i)) Then
        If LCase(Range("C" & i).Value) = "incident" Or LCase(Range("C" & i).Value) = "problem" Then
            If LCase(Range("c" & i).Value) <> "complete" And _
            LCase(Range("c" & i).Value) <> "rejected" And _
            LCase(Range("c" & i).Value) <> "testing" And _
            LCase(Range("c" & i).Value) <> "fixed" And _
            LCase(Range("c" & i).Value) <> "signoff" And _
            LCase(Range("c" & i).Value) <> "release" _
            Then Rows(i).Copy Destination:=Sheets("Sheet5").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
    End If
Next i
End Sub

 

[jedibrown]

Good morning - I just wondered if I could ask for your help again please on a similar issue to this just with different variables?