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]

Please post your actual code.

 

[jedibrown]

I have fixed it! It was because I was using capital letters for P&L apparently! I change them to lower cases and it's now picking them up.

Thanks again for all of your help - It has made my life a lot easier!

 

[jedibrown]

Sorry - I have thought of another problem.

Is there any way that I can ask it to look at column G and if it says "complete" to not paste that line?

 

[VoG]

Try like this

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 LCase(Range("G" & i).Value) <> "complete" And (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]

Perfect thank you - I think that should be it

Thanks again.

 

[jedibrown]

Hi - me again!

Would I also be able to get it to write over what was there before instead of adding it on?

Thank you.

 

[VoG]

Try

Private Sub CommandButton1_Click()
Dim LR As Long, i As Long
Sheets("Sheet2").UsedRange.ClearContents
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Not IsError(Range("B" & i)) Then
        If LCase(Range("G" & i).Value) <> "complete" And (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]

Brilliant - Thank you!

 

[jedibrown]

Hi - Sorry I'm back!

So the part which says, don't paste if row G is complete. Can you also let me know how to add -don't paste row g is complete or rejected?

Thank you!

 

[VoG]

Try

Private Sub CommandButton1_Click()
Dim LR As Long, i As Long
Sheets("Sheet2").UsedRange.ClearContents
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Not IsError(Range("B" & i)) Then
        If LCase(Range("G" & i).Value) <> "complete" And _
        LCase(Range("G" & i).Value) <> "rejected" And _
        (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