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]

Perfect - as usual - Thank you

 

[jedibrown]

Hi - Sorry me again.

Off the back of that very successful project yesterday - I have been asked to do another similar exercise.

I think the help you gave me yesterday just needs to be altered a bit but I am stuck at the 1st hurdle!

So I want it to do the same thing, pasting information into the next sheet but different criteria.

I want it to look for the word "request" in column C first off.

I then want it to paste all rows apart from the ones with the following in column G:

Complete
Rejected
Testing
Fixed
Signoff
Release

Please can you help me with this?

Many thanks

 

[jedibrown]

This was my attempt but it's not pasting anything into the next sheet

Dim LR As Long, i As Long
Sheets("Sheet2").UsedRange.ClearContents
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If Not IsError(Range("C" & i)) Then
If LCase(Range("G" & i).Value) <> "complete" And _
LCase(Range("G" & i).Value) <> "rejected" And _
LCase(Range("G" & i).Value) <> "testing" And _
LCase(Range("G" & i).Value) <> "fixed" And _
LCase(Range("G" & i).Value) <> "SignOff" And _
LCase(Range("G" & i).Value) <> "release" And _
(InStr(LCase(Range("C" & i).Value), "Request") > 0) _
Then Rows(i).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If

 

[VoG]

Try

Private Sub CommandButton1_Click()
Dim LR As Long, i As Long
Sheets("Sheet2").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) = "request" And _
        LCase(Range("G" & i).Value) <> "complete" And _
        LCase(Range("G" & i).Value) <> "rejected" And _
        LCase(Range("G" & i).Value) <> "testing" And _
        LCase(Range("G" & i).Value) <> "fixed" And _
        LCase(Range("G" & i).Value) <> "signoff" And _
        LCase(Range("G" & i).Value) <> "release" _
        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.

Can you tell me where I went wrong please? Hopefully if I can understand, I can stop bothering you!

Thanks

 

[jedibrown]

I have tried to change it so that I can use it for another thing too.

I want the same except it to go to sheet6 not sheet2 and it to look for incident, not requests in column c.

Private Sub CommandButton2_Click()
Dim LR As Long, i As Long
Sheets("sheet6").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" And _
LCase(Range("G" & i).Value) <> "complete" And _
LCase(Range("G" & i).Value) <> "rejected" And _
LCase(Range("G" & i).Value) <> "testing" And _
LCase(Range("G" & i).Value) <> "fixed" And _
LCase(Range("G" & i).Value) <> "signoff" And _
LCase(Range("G" & i).Value) <> "release" _
Then Rows(i).Copy Destination:=Sheets("sheet6").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub

I can't get it to paste the info in though - Any ideas?

 

[VoG]

incident needs to be in lower case

If LCase(Range("C" & i).Value) = "incident" And _

 

[jedibrown]

Sorry - just been asked if I can look for both incident and problem?

Can problem be added on please? Both in the same column.

You are such a star!

 

[VoG]

Try

If (LCase(Range("C" & i).Value) = "incident" or LCase(Range("C" & i).Value) = "problem") And _

 

[jedibrown]

You are amazing!! Thank you!!