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 LCase(Range("C" & i).Value) Like "* giant *" Then Rows(i).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
End Sub

 

[jedibrown]

Unfortunately - This doesn't seem to be working. I pasted it into VB within a command button.

Any ideas?

Thank you

 

[VoG]

Press ALT + F11 to open the Visual Basic Editor, select Module from the Insert menu and paste in the code. ALT + Q to close the code window.

You can assign this to a Forms

 

[jedibrown]

Thanks - I'm not sure what you mean by attach it to a form?

Sorry!

 

[jedibrown]

Sorry - I have also tried to run the module and it doesn't work either?

I have pasted

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

Gone to Macro and ran test.

 

[jedibrown]

Ok I have pasted this and ran it:

Sub test()
Dim LR As Long, i As Long
LR = Range("b" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If LCase(Range("c" & i).Value) Like "* giant *" Then Rows(i).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
End Sub

But it's only picking out 2 of the rows and there are lots more.

 

[VoG]

Try this

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

 

[jedibrown]

Thanks - This is coming up with the following error though:

Run time error 12

Type Mismatch

Thanks again for your help with this one

 

[jedibrown]

Sorry I meant Run time 13