Help with look up and row paste

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Feb 8, 2012

• #2

Try

Code:

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

 

Upvote 0

J

jedibrown

Board Regular

Joined

Oct 17, 2011

Messages

136

• Feb 8, 2012

• #3

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

Any ideas?

Thank you

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Feb 8, 2012

• #4

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

 

Upvote 0

J

jedibrown

Board Regular

Joined

Oct 17, 2011

Messages

136

• Feb 8, 2012

• #5

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

Sorry!

 

Upvote 0

J

jedibrown

Board Regular

Joined

Oct 17, 2011

Messages

136

• Feb 8, 2012

• #6

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.

 

Upvote 0

J

jedibrown

Board Regular

Joined

Oct 17, 2011

Messages

136

• Feb 8, 2012

• #7

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.

 

Last edited: Feb 8, 2012

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Feb 8, 2012

• #8

Try this

Code:

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

 

Upvote 0

J

jedibrown

Board Regular

Joined

Oct 17, 2011

Messages

136

• Feb 9, 2012

• #9

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

Run time error 12

Type Mismatch

Thanks again for your help with this one

 

Upvote 0

J

jedibrown

Board Regular

Joined

Oct 17, 2011

Messages

136

• Feb 9, 2012

• #10

Sorry I meant Run time 13

 

Upvote 0